如何扩展SQL查询以包含未生成的stat=0用户物品关联记录

要解决原查询无法覆盖未点击物品（table3无对应行）的问题，核心是先构建所有用户与所有物品的完整关联组合，再关联table3获取已有状态，缺失的状态用stat=0、dup=0填充，最后再执行差异对比逻辑。

修改后的查询语句 #

WITH full_user_item AS (
    -- 生成所有用户与所有物品的全组合
    SELECT 
        u.id AS user_id,
        i.id AS item_id
    FROM table1 u
    CROSS JOIN table2 i
),
user_item_status AS (
    -- 关联table3，填充缺失的stat和dup为0
    SELECT 
        fu.user_id,
        fu.item_id,
        COALESCE(t3.stat, 0) AS stat,
        COALESCE(t3.dup, 0) AS dup
    FROM full_user_item fu
    LEFT JOIN table3 t3 
        ON fu.user_id = t3.user_id 
        AND fu.item_id = t3.item_id
)
SELECT 
    t2.user_id,
    GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN t1.item_id END) `My Item List`,
    GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN t2.item_id END) `Item List`
FROM user_item_status t1
LEFT JOIN user_item_status t2 
    USING (item_id)
WHERE t1.user_id = @current_user
  AND t2.user_id <> @current_user
GROUP BY t2.user_id;

关键逻辑说明 #

1. 生成全量用户-物品组合：
   用CROSS JOIN将用户表（table1）和物品表（table2）做笛卡尔积，确保每个用户的每一件物品都有对应的记录，不管是否点击过。

2. 填充缺失的状态值：
   通过LEFT JOIN关联table3，用COALESCE函数把table3中不存在的记录的stat和dup字段默认设为0，符合需求中“未点击则stat=0、dup=0”的规则。

3. 执行差异对比：
   基于补全后的全量数据集执行原有的对比逻辑，此时就能覆盖所有物品的差异，包括未点击的物品。

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