SQL每日一题(20200514)

引自：https://mp.weixin.qq.com/s?__biz=MzA3MTg4NjY4Mw==&mid=2457305535&idx=4&sn=f6494b1b0f219045c3827f2e39c3ab79&chksm=88a5938bbfd21a9dde9a1493cc6b44f4a8b6effa77ad458dd6ebb2cdd7b1677524ec56de1079&mpshare=1&scene=1&srcid=&sharer_sharetime=1589466262994&sharer_shareid=18c156b37f741bf9989098e28bf33e09&exportkey=AckUEPXXeXtMd2FeqwM2Wrg%3D&pass_ticket=D5PcZKnc8BGkZ5DzEoHkHwZI2mk9uk%2BkH0FCbIBuKDBGm3NKEhExxA9uXweb0hmZ#rd

感谢出题者，如有冒犯，请与我联系，希望和大家一起学习交流。(目前使用oracle数据库环境编写sql)

 

题目

有如下ABC三列和几组数据

 

 

 

 

想得到如下结果

 

 

 

 

该如何写查询？

读者可以试着自己思考写下，再往下翻... 如有不同解题方式，欢迎大家一起交流。

 

1.运用聚合函数和dense_rank

sys@WIL> with t AS
  2      (SELECT 'aaa' A,'1' B,'X' C
  3      FROM dual
  4      UNION ALL
  5      SELECT 'aaa' A,'2' B,'Y' C
  6      FROM dual
  7      UNION ALL
  8      SELECT 'bbb' B,'3' B,'X' C
  9      FROM dual
 10      UNION ALL
 11      SELECT 'bbb' B,'4' B,'X' C
 12      FROM dual
 13      UNION ALL
 14      SELECT 'ccc' A,'5' B,'Y' C
 15      FROM dual
 16      UNION ALL
 17      SELECT 'ccc' A,'6' B,'Y' C
 18      FROM dual )
 19  SELECT DISTINCT A,
 20  BB,
 21  case
 22  WHEN max(RN) over(partition by A)>1 THEN
 23  '1'
 24  ELSE C
 25  END CC
 26  FROM
 27      (SELECT A,
 28  sum(B) over(partition by A) BB,
 29  C,
 30  dense_rank() over(partition by A
 31      ORDER BY  C) RN
 32      FROM t);

A           BB C
--- ---------- -
aaa          3 1
bbb          7 X
ccc         11 Y

 

2.运用lag函数：

sys@ESWIFT> with t AS
  2      (SELECT 'aaa' A,'1' B,'X' C
  3      FROM dual
  4      UNION ALL
  5      SELECT 'aaa' A,'2' B,'Y' C
  6      FROM dual
  7      UNION ALL
  8      SELECT 'bbb' B,'3' B,'X' C
  9      FROM dual
 10      UNION ALL
 11      SELECT 'bbb' B,'4' B,'X' C
 12      FROM dual
 13      UNION ALL
 14      SELECT 'ccc' A,'5' B,'Y' C
 15      FROM dual
 16      UNION ALL
 17      SELECT 'ccc' A,'6' B,'Y' C
 18      FROM dual )
 19  SELECT A,
 20  BB,
 21  CC
 22  FROM
 23      (SELECT A,
 24  sum(B) over(partition by A) BB,
 25  row_number()over(partition by A
 26      ORDER BY  A,B) rn,case
 27      WHEN c=lag(C) over(partition by A
 28      ORDER BY  A,B) THEN
 29      C
 30      ELSE '1'
 31      END CC
 32      FROM t)
 33  WHERE rn=2 ;

A           BB C
--- ---------- -
aaa          3 1
bbb          7 X
ccc         11 Y