如果多一行数据,如下: 列a 列b 1 a 2 b 1 c 2 d 1 e 2 f结果是否又多一列?? 列a 列b 列c 列d描述不太清楚?
SQL> with t as( 2 select 1 a,'a' b from dual union all 3 select 2,'b' from dual union all 4 select 1,'c' from dual union all 5 select 2,'d' from dual union all 6 select 3,'d' from dual union all 7 select 3,'e' from dual union all 8 select 2,'f' from dual) 9 select a,max(nvl(decode(rn,1,b),null)) b, 10 max(nvl(decode(rn,2,b),null)) c, 11 max(nvl(decode(rn,3,b),null)) d 12 from ( 13 select a,b,row_number() over (partition by a order by b) rn 14 from t) 15 group by a 16 /
列a 列b
1 a
2 b
1 c
2 d
1 e
2 f结果是否又多一列??
列a 列b 列c 列d描述不太清楚?
SQL> with t as(
2 select 1 a,'a' b from dual union all
3 select 2,'b' from dual union all
4 select 1,'c' from dual union all
5 select 2,'d' from dual union all
6 select 3,'d' from dual union all
7 select 3,'e' from dual union all
8 select 2,'f' from dual)
9 select a,max(nvl(decode(rn,1,b),null)) b,
10 max(nvl(decode(rn,2,b),null)) c,
11 max(nvl(decode(rn,3,b),null)) d
12 from (
13 select a,b,row_number() over (partition by a order by b) rn
14 from t)
15 group by a
16 /
A B C D
---------- - - -
1 a c
2 b d f
3 d e