感谢出题者，如有冒犯，请与我联系，希望和大家一起学习交流。(目前使用oracle数据库环境编写sql)
题目：
有如下两张表A和B
表A
表B
希望得到如下结果：
anum表示每个人参加的项目数，bnum表示每个人在各自项目中胜利的次数该如何写这个查询？
读者可以试着自己想写，再往下翻...
解答：
-
两表关联count聚合统计(null值不参与计算)
sys@WIL> WITH T AS 2 (SELECT '跑步' AID, '张三' BID 3 FROM DUAL 4 UNION ALL 5 SELECT '游泳' AID, '张三' BID 6 FROM DUAL 7 UNION ALL 8 SELECT '跳远' AID, '李四' BID 9 FROM DUAL 10 UNION ALL 11 SELECT '跳高' AID, '王五' BID 12 FROM DUAL), 13 TT AS 14 (SELECT '跑步' AID, '张三' BID, '胜' CID 15 FROM DUAL 16 UNION ALL 17 SELECT '游泳' AID, '张三' BID, '胜' CID 18 FROM DUAL 19 UNION ALL 20 SELECT '跳高' AID, '王五' BID, '胜' CID 21 FROM DUAL) 22 SELECT T.BID, COUNT(T.BID) ANUM, COUNT(TT.CID) BNUM 23 FROM T 24 LEFT JOIN TT 25 ON T.BID = TT.BID 26 AND T.AID = TT.AID 27 GROUP BY T.BID 28 ORDER BY 2 DESC, 1; BID ANUM BNUM ---- ---------- ---------- 张三 2 2 李四 1 0 王五 1 1
- 分别对表A和表B进行聚合后再关联
sys@WIL> WITH T AS 2 (SELECT '跑步' AID, '张三' BID 3 FROM DUAL 4 UNION ALL 5 SELECT '游泳' AID, '张三' BID 6 FROM DUAL 7 UNION ALL 8 SELECT '跳远' AID, '李四' BID 9 FROM DUAL 10 UNION ALL 11 SELECT '跳高' AID, '王五' BID 12 FROM DUAL), 13 TT AS 14 (SELECT '跑步' AID, '张三' BID, '胜' CID 15 FROM DUAL 16 UNION ALL 17 SELECT '游泳' AID, '张三' BID, '胜' CID 18 FROM DUAL 19 UNION ALL 20 SELECT '跳高' AID, '王五' BID, '胜' CID 21 FROM DUAL) 22 SELECT T3.BID, T3.CN ANUM, NVL(T4.CN, 0) BNUM 23 FROM (SELECT BID, COUNT(*) CN FROM T GROUP BY BID) T3 24 LEFT JOIN (SELECT BID, COUNT(*) CN FROM TT GROUP BY BID) T4 25 ON T3.BID = T4.BID 26 ORDER BY 2 DESC, 1; BID ANUM BNUM ---- ---------- ---------- 张三 2 2 李四 1 0 王五 1 1
- oracle特有语法(数据量少时，可先对关联查询表进行谓词推进，12c可以用lateral实现)：
sys@WIL> WITH T AS 2 (SELECT '跑步' AID, '张三' BID 3 FROM DUAL 4 UNION ALL 5 SELECT '游泳' AID, '张三' BID 6 FROM DUAL 7 UNION ALL 8 SELECT '跳远' AID, '李四' BID 9 FROM DUAL 10 UNION ALL 11 SELECT '跳高' AID, '王五' BID 12 FROM DUAL), 13 TT AS 14 (SELECT '跑步' AID, '张三' BID, '胜' CID 15 FROM DUAL 16 UNION ALL 17 SELECT '游泳' AID, '张三' BID, '胜' CID 18 FROM DUAL 19 UNION ALL 20 SELECT '跳高' AID, '王五' BID, '胜' CID 21 FROM DUAL) 22 SELECT T3.*,T4.COLUMN_VALUE BNUM FROM (SELECT BID, COUNT(*)ANUM FROM T GROUP BY BID) T3, 23 TABLE(CAST(MULTISET 24 (SELECT COUNT(*) CN FROM TT WHERE BID = T3.BID) AS 25 SYS.ODCIVARCHAR2LIST)) T4 26 ORDER BY 2 DESC, 1; BID ANUM BNUM ---- ---------- ------------------------------ 张三 2 2 李四 1 0 王五 1 1
如有其它解题思路，欢迎一起交流学习。
