SQL每日一题(20200514)

题目

有如下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

SQL每日一题(20200514)

标签：col   tin   rgb   运用   查询   data-   nbsp   exp   思考