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