参考一下这个,懒得改了select column2 ,column3, max(substr(sys_connect_by_path(column1, ','), 2)) column1 from (
select column1,column2 ,column3, colc, lead(colc) over(partition by id order by colc) cold from (
select column1,column2 ,column3, row_number() over(order by column2 ,column3) colc from tab) )
start with cold is null
connect by prior colc=cold
group by column2 ,column3;
select column1,column2 ,column3, colc, lead(colc) over(partition by id order by colc) cold from (
select column1,column2 ,column3, row_number() over(order by column2 ,column3) colc from tab) )
start with cold is null
connect by prior colc=cold
group by column2 ,column3;
请问能写详细点么?谢谢
(
ID VARCHAR2(20),
NAME VARCHAR2(100),
ADDRESS VARCHAR2(100),
TEL VARCHAR2(100),
NO VARCHAR2(100)
);
INSERT INTO T003 VALUES('H76','胡A','敬龙布艺城','23360313','HH003269905CN');
INSERT INTO T003 VALUES('H47','吴A','志达','23882238','HH003270316CN');
INSERT INTO T003 VALUES('H21','叶A','沙头大道140号','13XXX828071','HH003270611CN');
INSERT INTO T003 VALUES('H49','叶A','沙头大道140号','13XXX828071','HH003270611CN');
INSERT INTO T003 VALUES('H97','叶A','沙头大道140号','13XXX828071','HH003270611CN');
INSERT INTO T003 VALUES('H00','叶A','沙头大道140号','13XXX828071','HH003270611CN');
INSERT INTO T003 VALUES('H73','苏A','晖楼强虹公司','13XXX930094','HH003273768CN');
INSERT INTO T003 VALUES('H92','苏A','晖楼强虹公司','13XXX930094','HH003273768CN');
INSERT INTO T003 VALUES('H48','苏A','晖楼强虹公司','13XXX930094','HH003273768CN');
SELECT REPLACE(WM_CONCAT(ID),',','/') AS ID,NAME,ADDRESS,TEL,NO
FROM T003
GROUP BY NO,NAME,ADDRESS,TEL;ID NAME ADDRESS TEL NO
H76 胡A 敬龙布艺城 23360313 HH003269905CN
H47 吴A 志达 23882238 HH003270316CN
H21/H00/H49/H97 叶A 沙头大道140号 13XXX828071 HH003270611CN
H73/H48/H92 苏A 晖楼强虹公司 13XXX930094 HH003273768CN