※ 引述《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