Re: [SQL] 面試碰到一題不會QQ
: 如圖，給定兩個表，任務是產生一個表，該表必須包含每個人每個種類的交易記錄（若有
: 多筆則加總），如無交易記錄，則為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://www.ptt.cc/bbs/Database/M.1575910498.A.2B4.html
※ 編輯: shadowjohn (1.168.33.189 臺灣), 12/10/2019 01:03:17
討論串 (同標題文章)
Database 近期熱門文章
PTT數位生活區 即時熱門文章