我有一个这样的表:
+----+-----+------+
| id | ord | test |
+----+-----+------+
| 1 | 1 | A |
| 1 | 2 | B |
| 1 | 3 | C |
| 2 | 1 | B |
| 2 | 2 | C |
+----+-----+------+
(这是一些创建数据的代码(
drop table temp_test;
create table temp_test (id varchar(20), ord varchar(20), test varchar(20));
insert into temp_test (id,ord,test) values ('1','1','A');
insert into temp_test (id,ord,test) values ('1','2','B');
insert into temp_test (id,ord,test) values ('1','3','C');
insert into temp_test (id,ord,test) values ('2','1','B');
insert into temp_test (id,ord,test) values ('2','2','C');
commit;
我怎样才能得到以下结果?
+----+-----+-------+
| id | ord | test |
+----+-----+-------+
| 1 | 1 | A |
| 1 | 2 | A_B |
| 1 | 3 | A_B_C |
| 2 | 1 | B |
| 2 | 2 | B_C |
+----+-----+-------+
我尝试过使用LAG((,类似于:
select CONCAT(lag(TEST) over (partition by ID order by ord),TEST) AS TEST from temp_test;
但是它不能递归地工作。
此代码有效:
SELECT
R1.*,
( SELECT LISTAGG(test, ';') WITHIN GROUP (ORDER BY ord)
FROM temp_test R2
WHERE R1.ord >= R2.ord
AND R1.ID = R2.ID
GROUP BY ID
) AS WTR_KEYWORD_1
FROM temp_test R1
ORDER BY id, ord;
但对于更大的数据集来说,它的性能还不够。
有些人说层次查询已经过时,但它们通常比递归CTE 执行得更好
SELECT id,
ord,
LTRIM(sys_connect_by_path(test,'_'),'_') as test
FROM temp_test r2 START WITH ord = 1 -- use MIN() to get this if it's not always 1
CONNECT BY PRIOR id = id AND ord = PRIOR ord + 1;
演示
您可以使用递归cte来实现此
with cte(id,ord,test,concat_val)
as (select id,ord,test,test as concat_val
from temp_test
where ord=1
union all
select a.id,a.ord,a.test,b.concat_val||'_'||a.test
from temp_test a
join cte b
on a.id=b.id
and a.ord=b.ord+1
)
select * from cte order by id,ord
在这里演示
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=78baa20f7f364e653899caf63ce7ada2