如何关联TransactionList与ItemList并生成全量商品交易结果集？

要实现每个交易ID对应所有商品的记录（无对应商品时数量显示0），确实需要用到Cross Join，结合子查询和左连接就能完成，具体实现如下：

核心思路 #

1. 先提取交易表中所有唯一的交易ID，避免生成重复无效的组合；
2. 将唯一交易ID集合与商品表做笛卡尔积（Cross Join），得到每个交易ID对应所有商品的基础行；
3. 左连接原交易表匹配对应交易和商品的数量，未匹配到的数量用0填充。

实现SQL代码 #

SELECT 
    t_unique.id,
    i.id AS item,
    COALESCE(t.quantity, 0) AS quantity,
    i.name
FROM 
    -- 提取所有不重复的交易ID
    (SELECT DISTINCT id FROM TransactionList) t_unique
-- 生成交易ID与所有商品的全组合
CROSS JOIN 
    ItemList i
-- 左连接原交易表，匹配对应交易和商品的数量
LEFT JOIN 
    TransactionList t 
    ON t_unique.id = t.id AND i.id = t.item
-- 按交易ID、商品ID排序，与预期结果顺序一致
ORDER BY 
    t_unique.id, i.id;

代码说明 #

• (SELECT DISTINCT id FROM TransactionList)：从交易表中取出所有唯一的交易ID，确保每个交易只生成一次全商品组合；
• CROSS JOIN ItemList：这一步是核心，它会把每个交易ID和所有商品进行配对，保证每个交易都包含全部商品的行；
• LEFT JOIN TransactionList：保留所有全组合行，同时关联原交易表中已存在的交易-商品记录，未关联到的行quantity会是NULL；
• COALESCE(t.quantity, 0)：将NULL的数量替换为0，满足“无对应商品时数量为0”的要求；
• ORDER BY：按照交易ID和商品ID排序，输出结果与你给出的预期格式完全一致。

执行这段SQL后，就能得到你需要的目标结果表。

内容的提问来源于stack exchange，提问作者cy yung