SQL查询如何包含未生成的stat=0且dup=0的用户-物品关系行？

我有三张表：用户表table1、物品表table2，以及记录用户与物品关联关系的table3。其中table3的stat字段（取值0/1）表示用户是否拥有物品，dup字段（取值0/1）表示是否拥有副本。只有当用户点击物品或副本按钮时，才会在table3中新增或更新对应行。

我需要对比当前用户与其他用户的物品差异，但现有查询只能统计table3中已有记录的物品，无法纳入那些未生成记录（即默认stat=0且dup=0）的物品。

表结构 #

Table1（用户表） #

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

Table2（物品表） #

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

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示例数据：

原查询 #

我原本使用的对比查询：

SELECT t2.user_id,
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN item_id END) `My Item List`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN item_id END) `Item List`
FROM table3 t1
LEFT JOIN table3 t2 USING (item_id)
WHERE t1.user_id = @current_user
  AND t2.user_id <> @current_user
GROUP BY t2.user_id

问题点 #

上述查询仅能对比table3中已有记录的物品，对于那些用户从未点击过按钮、未在table3生成行的物品，默认stat=0且dup=0，这些物品没有被纳入统计，需要修改查询以包含这类情况。

要包含所有物品（无论是否在table3中有记录），需要先生成所有用户与所有物品的全量组合，再关联table3获取实际的stat和dup值，不存在的记录默认补0。

修改后的查询 #

SELECT other_users.id AS user_id,
       GROUP_CONCAT(CASE WHEN current_user_stats.dup = 1 AND COALESCE(other_user_stats.stat, 0) = 0 THEN items.id END) `My Item List`,
       GROUP_CONCAT(CASE WHEN COALESCE(other_user_stats.dup, 0) = 1 AND COALESCE(current_user_stats.stat, 0) = 0 THEN items.id END) `Item List`
FROM table1 other_users
CROSS JOIN table2 items
LEFT JOIN table3 current_user_stats 
  ON current_user_stats.user_id = @current_user 
  AND current_user_stats.item_id = items.id
LEFT JOIN table3 other_user_stats 
  ON other_user_stats.user_id = other_users.id 
  AND other_user_stats.item_id = items.id
WHERE other_users.id <> @current_user
GROUP BY other_users.id;

核心逻辑说明 #

1. 全量组合生成：通过table1 other_users CROSS JOIN table2 items生成所有其他用户与所有物品的组合，确保没有遗漏任何物品。
2. 默认值补全：使用LEFT JOIN关联table3，并通过COALESCE函数将不存在的记录的stat和dup默认设为0，匹配未生成行的默认状态。
3. 对比逻辑保留：在GROUP_CONCAT的条件判断中，兼容补全后的默认值，确保未生成记录的物品也能参与差异对比。

性能优化建议 #

如果用户和物品数据量较大，可通过以下方式优化：

• 提前筛选目标对比用户，缩小笛卡尔积的范围
• 给table3建立user_id + item_id的联合索引：CREATE INDEX idx_user_item ON table3(user_id, item_id);

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