select column2 ,column3, max(substr(sys_connect_by_path(column1, ','), 2)) column1 from (
select 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 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;
column1有问题partition by id order by colc
id是什么?
column1 column2
a 1
a 2
a 3
b 2
b 3
b 4
b 5
c 3
c 2
c 1
d 1
d 2
d 3
d 4
结果要求为
column1 column2
a,c 1
a,c 2
a,c 3
b 2
b 3
b 4
b 5
d 1
d 2
d 3
d 4
也就是以当column2的个数和值完全相同时column1就以逗号隔开分组,如:前面的column1的a和c的column2的值都是1、2、3
SQL 要怎么写啊??
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;
你那种好像是当column2相同的时候column1就以逗号分隔,
不是我要的形式