引自：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 1bbb 7 Xccc 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 1bbb 7 Xccc 11 Y