作者shadowjohn (转角遇到爱)
看板Database
标题Re: [SQL] 面试碰到一题不会QQ
时间Tue Dec 10 00:54:54 2019
※ 引述《deniel367 (dann)》之铭言：
: https://i.imgur.com/Gh8nZjG.jpg
: 如图，给定两个表，任务是产生一个表，该表必须包含每个人每个种类的交易记录（若有
: 多笔则加总），如无交易记录，则为0
: 请问这题SQL query该怎麽写？感谢！
select user_id,B.type,
sum( case when A.type = B.type then A.revenue else 0 end ) AS totals
from TableA A cross join TableB B
LEFT JOIN (SELECT type,@row as fakeRow From TableB) BB ON 1=1 AND
B.type=BB.type
group by user_id,B.type
order by user_id,BB.fakeRow
小调整，连排序也作完成度比较高
https://i.imgur.com/ZqTkZm0.png
补一个没 cross 的版本，没用过cross (烟~
SELECT AA.user_id,AA.type,SUM(AA.totals) AS `totals`
FROM
(
SELECT C.user_id,C.type,C.totals
FROM
(
select DISTINCT A.user_id,B.type, 0 AS `totals`
from
TableB AS B,
TableA AS A
) C
UNION ALL
select user_id,type,revenue AS totals from TableA
) AA
LEFT OUTER JOIN
(SELECT type,@row AS row FROM TableB) AS B on 1=1
AND AA.type=B.type
GROUP BY AA.user_id,AA.type
ORDER BY AA.user_id,B.row
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 1.168.33.189 (台湾)
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1575910498.A.2B4.html
※ 编辑: shadowjohn (1.168.33.189 台湾), 12/10/2019 01:03:17