合并两个SQL查询：获取用户可发货物品对比列表需求

需求 #

将两个独立SQL查询合并为一个，输出包含以下字段的结果表：

• 其他用户的用户名
• MY ITEM LIST：用户17拥有、但当前用户未拥有的可发货物品列表
• ITEM LIST：当前用户拥有、但用户17未拥有的可发货物品列表

原SQL问题修正 #

原SQL存在两处问题：

1. SQL1的子查询缺少闭合括号，且无需返回全字段，仅需item_id即可
2. 表名不匹配：原SQL中accounts对应表结构里的table1，用户名字段为user_name

合并方案（兼容MySQL） #

由于MySQL不支持全外连接，采用LEFT JOIN + UNION + RIGHT JOIN的方式合并结果，确保所有符合条件的用户都被包含：

SELECT 
    COALESCE(a.user_id, b.user_id) AS user_id,
    COALESCE(a.Username, b.Username) AS Username,
    b.`MY Item List`,
    a.`Item List`
FROM 
    (
        -- 其他用户有、用户17未拥有的可发货物品
        SELECT 
            t1.id AS user_id,
            MAX(t1.user_name) AS 'Username',
            GROUP_CONCAT(t2.item_no ORDER BY t2.id ASC SEPARATOR ', ') AS 'Item List'
        FROM table3 t3
        INNER JOIN table1 t1 ON t3.user_id = t1.id
        INNER JOIN table2 t2 ON t3.item_id = t2.id
        INNER JOIN (
            SELECT item_id FROM table3 WHERE user_id = 17 AND stat = 0
        ) AS c ON t3.item_id = c.item_id 
        WHERE t3.shipped = 1 AND t3.user_id != 17
        GROUP BY t1.id
    ) a
LEFT JOIN 
    (
        -- 用户17有、其他用户未拥有的物品
        SELECT 
            t1.id AS user_id,
            MAX(t1.user_name) AS 'Username',
            GROUP_CONCAT(t2.item_no ORDER BY t2.id ASC SEPARATOR ', ') AS 'MY Item List'
        FROM table3 t3
        INNER JOIN table1 t1 ON t3.user_id = t1.id
        INNER JOIN table2 t2 ON t3.item_id = t2.id
        INNER JOIN (
            SELECT item_id FROM table3 WHERE user_id = 17 AND shipped = 1
        ) AS d ON t3.item_id = d.item_id
        WHERE t3.stat = 0 AND t3.user_id != 17
        GROUP BY t1.id
    ) b ON a.user_id = b.user_id
UNION
SELECT 
    COALESCE(a.user_id, b.user_id) AS user_id,
    COALESCE(a.Username, b.Username) AS Username,
    b.`MY Item List`,
    a.`Item List`
FROM 
    (
        -- 其他用户有、用户17未拥有的可发货物品
        SELECT 
            t1.id AS user_id,
            MAX(t1.user_name) AS 'Username',
            GROUP_CONCAT(t2.item_no ORDER BY t2.id ASC SEPARATOR ', ') AS 'Item List'
        FROM table3 t3
        INNER JOIN table1 t1 ON t3.user_id = t1.id
        INNER JOIN table2 t2 ON t3.item_id = t2.id
        INNER JOIN (
            SELECT item_id FROM table3 WHERE user_id = 17 AND stat = 0
        ) AS c ON t3.item_id = c.item_id 
        WHERE t3.shipped = 1 AND t3.user_id != 17
        GROUP BY t1.id
    ) a
RIGHT JOIN 
    (
        -- 用户17有、其他用户未拥有的物品
        SELECT 
            t1.id AS user_id,
            MAX(t1.user_name) AS 'Username',
            GROUP_CONCAT(t2.item_no ORDER BY t2.id ASC SEPARATOR ', ') AS 'MY Item List'
        FROM table3 t3
        INNER JOIN table1 t1 ON t3.user_id = t1.id
        INNER JOIN table2 t2 ON t3.item_id = t2.id
        INNER JOIN (
            SELECT item_id FROM table3 WHERE user_id = 17 AND shipped = 1
        ) AS d ON t3.item_id = d.item_id
        WHERE t3.stat = 0 AND t3.user_id != 17
        GROUP BY t1.id
    ) b ON a.user_id = b.user_id
ORDER BY 
    LENGTH(COALESCE(`Item List`, '')) + LENGTH(COALESCE(`MY Item List`, '')) DESC;

方案说明 #

1. 子查询保留原逻辑：分别获取两类物品数据，和原SQL的筛选规则一致
2. 关联处理空值：通过COALESCE函数处理仅存在于其中一个子查询的用户数据，避免字段为空
3. 模拟全外连接：用LEFT JOIN+RIGHT JOIN结合UNION，确保所有符合条件的用户都能出现在结果中
4. 排序保持一致：按两个物品列表的总长度降序排列，和原SQL的排序逻辑匹配

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