有一个表 temp
字段有 id date_id数据有:
001 20110501
001 20110502
002 20110501
002 20110502
002 20110503现在要得到
001 20110501,20110502
002 20110501,20110502,20110503这个sql要怎么写,查询速度要快点的。select id,max(sys_connect_by_path(date_id, ',')) date_id
from (select id,date_id, row_number() over(PARTITION BY id order by date_id) rn
from temp_lizx0506 t
)
start WITH rn = 1
connect by PRIOR rn = rn - 1
GROUP BY id;我这样写的查询很久数据还不出来!
字段有 id date_id数据有:
001 20110501
001 20110502
002 20110501
002 20110502
002 20110503现在要得到
001 20110501,20110502
002 20110501,20110502,20110503这个sql要怎么写,查询速度要快点的。select id,max(sys_connect_by_path(date_id, ',')) date_id
from (select id,date_id, row_number() over(PARTITION BY id order by date_id) rn
from temp_lizx0506 t
)
start WITH rn = 1
connect by PRIOR rn = rn - 1
GROUP BY id;我这样写的查询很久数据还不出来!
with temp as(
select '001' id,'20110501' date_id from dual
union all
select '001','20110502' from dual
union all
select '002','20110501' from dual
union all
select '002','20110502' from dual
union all
select '002','20110503' from dual
)select id,wm_concat(date_id) from temp group by id001 20110501,20110502
002 20110501,20110503,20110502
select id,wm_concat(date_id) from temp group by id