SQL查询需求：对比当前用户与其他用户的物品差异

我有三张表：用户列表表、物品表以及用户与物品的关联表。用户可拥有物品（stat=0/1）或拥有重复物品（dup=0/1）。

表结构 #

Table1（用户表） #

CREATE TABLE table1(
    id NOT NULL AUTO_INCREMENT,
    user_name varchar(255),
);

Table2（物品表） #

CREATE TABLE table2(
    id NOT NULL AUTO_INCREMENT,
    item_no varchar(255),
    item_name varchar(255),
    item_group varchar(255)
);

Table3（用户物品关联表） #

CREATE TABLE table3 (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  item_id int NOT NULL,
  stat tinyint NOT NULL,
  dup tinyint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES table1(id),
  FOREIGN KEY (item_id) REFERENCES table2(id)
);

Table3示例数据 #

期望查询结果（当前登录用户ID为17） #

字段规则 #

• My Item List：当前用户（userid=17）dup=1，且其他用户（userid!=17）对该物品的stat=0——即当前用户拥有重复物品，但其他用户未拥有该物品。
• Item List：其他用户（userid!=17）dup=1，且当前用户（userid=17）对该物品的stat=0——即其他用户拥有重复物品，但当前用户未拥有该物品。

存在问题的SQL #

SELECT 
  max(t1.username) AS 'Username',
  group_concat(DISTINCT CASE WHEN t3_2.user_id = 17 THEN t2.item_no END ORDER BY t2.id ASC separator ', ') AS 'MY Item List',
  group_concat(DISTINCT CASE WHEN t3_1.user_id != 17 THEN t2.item_no END ORDER BY t2.id ASC separator ', ') AS 'Item List'
FROM 
  table3 t3_1
INNER JOIN 
  table1 t1 
    ON t3_1.user_id = t1.id
INNER JOIN 
  table2 t2 
    ON t3_1.item_id = t2.id
LEFT JOIN 
  table3 t3_2 
    ON  t3_2.item_id = t3_1.item_id 
    AND t3_2.user_id = 17 
    AND t3_2.dup = 1
WHERE 
  t3_1.dup = 1 
  AND t3_1.item_id != 17 
  AND t3_1.stat = 0
GROUP BY 
  t1.id
ORDER BY 
  COUNT(DISTINCT t2.item_no) DESC;

该SQL存在部分物品和信息缺失的问题，需要修正。

原SQL问题分析 #

1. WHERE条件中的t3_1.item_id != 17是错误逻辑，混淆了物品ID与用户ID，需删除。
2. 关联逻辑未准确匹配两个字段的规则：My Item List需基于当前用户的重复物品匹配其他用户未拥有的情况，Item List需基于其他用户的重复物品匹配当前用户未拥有的情况，原SQL从单一维度查询导致数据遗漏。
3. 未处理无符合条件物品时的空值展示，不符合期望结果的---格式。

修正后的SQL #

WITH user_17_items AS (
    -- 预提取当前用户17的所有物品状态
    SELECT item_id, stat, dup
    FROM table3
    WHERE user_id = 17
)
SELECT
    t1.user_name AS UserName,
    COALESCE(GROUP_CONCAT(DISTINCT CASE 
        WHEN u17.dup = 1 AND t3.stat = 0 THEN t2.item_no 
    END ORDER BY t2.id ASC SEPARATOR ', '), '---') AS 'My Item List',
    COALESCE(GROUP_CONCAT(DISTINCT CASE 
        WHEN t3.dup = 1 AND u17.stat = 0 THEN t2.item_no 
    END ORDER BY t2.id ASC SEPARATOR ', '), '---') AS 'Item List'
FROM
    table1 t1
JOIN table3 t3 ON t1.id = t3.user_id
JOIN table2 t2 ON t3.item_id = t2.id
LEFT JOIN user_17_items u17 ON t3.item_id = u17.item_id
WHERE
    t1.id != 17
GROUP BY
    t1.id, t1.user_name
ORDER BY
    t1.id;

逻辑说明 #

1. CTE user_17_items：提前获取当前用户17的所有物品状态，避免重复查询，提升效率。
2. 字段匹配逻辑：
   • My Item List：筛选当前用户dup=1且其他用户stat=0的物品，拼接编号。
   • Item List：筛选其他用户dup=1且当前用户stat=0的物品，拼接编号。
3. COALESCE函数：当无符合条件物品时，用---替代空值，匹配期望结果格式。

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