select aa,aa_path from( select aa ,rank,aa_path,row_number()over(partition by aa order by rank desc) aa_path_rank from (select aa,bb,rank,ltrim(sys_connect_by_path(bb,','),',') aa_path from( select aa ,bb ,row_number()over(partition by aa order by id) rank from test(表名) order by aa) connect by name = prior aa and rank-1=prior rank)) where aa_path_rank=1;试试这个。
SELECT aa, REPLACE(wmsys.wm_concat(bb), ',' ,',') FROM table GROUP BY aa
SELECT AA,WM_CONCAT(BB) BB FROM TABLE_NAME GROUP BY AA;
...wm_concat啥玩意,我这提示未知的对象,我是9i的
select aa,wm_concat(bb) from temp group by aa
select aa,max(substr(sys_connect_by_path(bb,','),2)) from (select aa,bb,row_number() over(partition by aa order by aa) rn from yourtable) start with rn=1 connect by rn=rownum group by aa
上面的错了,改为: select aa,max(substr(sys_connect_by_path(name,','),2)) from (select aa,bb,row_number() over(partition by aa order by aa) rn from yourtable) start with rn=1 connect by prior aa=aa and prior rn=rn-1 group by aa
select aa ,rank,aa_path,row_number()over(partition by aa order by rank desc) aa_path_rank from
(select aa,bb,rank,ltrim(sys_connect_by_path(bb,','),',') aa_path
from(
select aa ,bb ,row_number()over(partition by aa order by id) rank
from test(表名) order by aa) connect by name = prior aa and rank-1=prior rank))
where aa_path_rank=1;试试这个。
FROM table
GROUP BY aa
SELECT AA,WM_CONCAT(BB) BB
FROM TABLE_NAME
GROUP BY AA;
select aa,max(substr(sys_connect_by_path(bb,','),2)) from
(select aa,bb,row_number() over(partition by aa order by aa) rn from yourtable)
start with rn=1 connect by rn=rownum
group by aa
select aa,max(substr(sys_connect_by_path(name,','),2)) from
(select aa,bb,row_number() over(partition by aa order by aa) rn from yourtable)
start with rn=1 connect by prior aa=aa
and prior rn=rn-1
group by aa
test(表名) 这个test改为你那个表名,就可以了