select col1 , max(col2) , max(col3) , max(col4) from mytable group by col1
SQL> WITH T AS( 2 SELECT '01' COL1,'aa1' col2,null col3,null col4 from dual union all 3 SELECT '01' COL1,null col2,'bb1' col3,null col4 from dual union all 4 SELECT '01' COL1,null col2,null col3,'cc1' col4 from dual union all 5 SELECT '01' COL1,null col2,'bb2' col3,null col4 from dual union all 6 SELECT '02' COL1,'AA1' col2,'BB1' col3,null col4 from dual union all 7 SELECT '02' COL1,NULL col2,null col3,'CC1' col4 from dual 8 ),T1 AS( 9 SELECT COL1,MAX(COL2)KEEP(DENSE_RANK LAST ORDER BY ROWNUM) COL2 FROM T 10 WHERE COL2 IS NOT NULL 11 GROUP BY COL1 12 ),T2 AS( 13 SELECT COL1,MAX(COL3)KEEP(DENSE_RANK LAST ORDER BY ROWNUM) COL3 FROM T 14 WHERE COL3 IS NOT NULL 15 GROUP BY COL1 16 ),T3 AS( 17 SELECT COL1,MAX(COL4)KEEP(DENSE_RANK LAST ORDER BY ROWNUM) COL4 FROM T 18 WHERE COL4 IS NOT NULL 19 GROUP BY COL1 20 ) 21 select T1.COL1,T1.COL2,T2.COL3,T3.COL4 from T1,T2,T3 22 WHERE T1.COL1=T2.COL1 AND T1.COL1=T3.COL1;CO COL COL COL -- --- --- --- 01 aa1 bb2 cc1 02 AA1 BB1 CC1SQL>
简化了下 SQL> WITH T AS( 2 SELECT '01' COL1,'aa1' col2,null col3,null col4 from dual union all 3 SELECT '01' COL1,null col2,'bb1' col3,null col4 from dual union all 4 SELECT '01' COL1,null col2,null col3,'cc1' col4 from dual union all 5 SELECT '01' COL1,null col2,'bb2' col3,null col4 from dual union all 6 SELECT '02' COL1,'AA1' col2,'BB1' col3,null col4 from dual union all 7 SELECT '02' COL1,NULL col2,null col3,'CC1' col4 from dual 8 ) 9 SELECT COL1, 10 MAX(COL2)KEEP(DENSE_RANK LAST ORDER BY COL2 NULLS FIRST,ROWNUM) COL2, 11 MAX(COL3)KEEP(DENSE_RANK LAST ORDER BY COL3 NULLS FIRST,ROWNUM) COL3, 12 MAX(COL4)KEEP(DENSE_RANK LAST ORDER BY COL4 NULLS FIRST,ROWNUM) COL4 13 FROM T 14 GROUP BY COL1;CO COL COL COL -- --- --- --- 01 aa1 bb2 cc1 02 AA1 BB1 CC1SQL>
select col1 , max(col2) , max(col3) , max(col4)
from mytable
group by col1
2 SELECT '01' COL1,'aa1' col2,null col3,null col4 from dual union all
3 SELECT '01' COL1,null col2,'bb1' col3,null col4 from dual union all
4 SELECT '01' COL1,null col2,null col3,'cc1' col4 from dual union all
5 SELECT '01' COL1,null col2,'bb2' col3,null col4 from dual union all
6 SELECT '02' COL1,'AA1' col2,'BB1' col3,null col4 from dual union all
7 SELECT '02' COL1,NULL col2,null col3,'CC1' col4 from dual
8 ),T1 AS(
9 SELECT COL1,MAX(COL2)KEEP(DENSE_RANK LAST ORDER BY ROWNUM) COL2 FROM T
10 WHERE COL2 IS NOT NULL
11 GROUP BY COL1
12 ),T2 AS(
13 SELECT COL1,MAX(COL3)KEEP(DENSE_RANK LAST ORDER BY ROWNUM) COL3 FROM T
14 WHERE COL3 IS NOT NULL
15 GROUP BY COL1
16 ),T3 AS(
17 SELECT COL1,MAX(COL4)KEEP(DENSE_RANK LAST ORDER BY ROWNUM) COL4 FROM T
18 WHERE COL4 IS NOT NULL
19 GROUP BY COL1
20 )
21 select T1.COL1,T1.COL2,T2.COL3,T3.COL4 from T1,T2,T3
22 WHERE T1.COL1=T2.COL1 AND T1.COL1=T3.COL1;CO COL COL COL
-- --- --- ---
01 aa1 bb2 cc1
02 AA1 BB1 CC1SQL>
SQL> WITH T AS(
2 SELECT '01' COL1,'aa1' col2,null col3,null col4 from dual union all
3 SELECT '01' COL1,null col2,'bb1' col3,null col4 from dual union all
4 SELECT '01' COL1,null col2,null col3,'cc1' col4 from dual union all
5 SELECT '01' COL1,null col2,'bb2' col3,null col4 from dual union all
6 SELECT '02' COL1,'AA1' col2,'BB1' col3,null col4 from dual union all
7 SELECT '02' COL1,NULL col2,null col3,'CC1' col4 from dual
8 )
9 SELECT COL1,
10 MAX(COL2)KEEP(DENSE_RANK LAST ORDER BY COL2 NULLS FIRST,ROWNUM) COL2,
11 MAX(COL3)KEEP(DENSE_RANK LAST ORDER BY COL3 NULLS FIRST,ROWNUM) COL3,
12 MAX(COL4)KEEP(DENSE_RANK LAST ORDER BY COL4 NULLS FIRST,ROWNUM) COL4
13 FROM T
14 GROUP BY COL1;CO COL COL COL
-- --- --- ---
01 aa1 bb2 cc1
02 AA1 BB1 CC1SQL>