有关于查询的问题

[悬崖跳舞被人砍]

我有一表，数据示例如下：

每一条数据都是按照插入时间的先后依次显示的，现想实现下面的查询结果：

请问如何用语句实现

[悬崖跳舞被人砍]

引用 3 楼 bw555 的回复:

简化了下

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 CC1

SQL>

斑竹，你为啥米这么屌~

[卖水果的net]

select col1 , max(col2) , max(col3) , max(col4) 
from mytable
group by col1

[bw555]

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 CC1

SQL>

[bw555]

简化了下

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 CC1

SQL>

[悬崖跳舞被人砍]

引用 3 楼 bw555 的回复:

简化了下

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 CC1

SQL>

怪我没有表述清楚，其实数据不只是这么几条记录的，像我贴图贴出来的数据很多的，这样一条一条写是不行的~

[悬崖跳舞被人砍]

引用 3 楼 bw555 的回复:

简化了下

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 CC1

SQL>

我错了斑竹 还没看清楚你的回答就答复你了`