如何查找数据库中表A缺失的表B设置项？

这个需求在日常数据库操作里挺常见的，核心思路就是先生成所有用户和所有可能设置的完整组合，再排除用户已经拥有的设置，剩下的就是缺失的部分。我结合你的示例来一步步说明怎么实现。

示例表结构与数据 #

先明确你的表结构和测试数据，方便对照理解：

表A（信息数据表）

CREATE TABLE table_a (
    username VARCHAR(50),
    setting INT
);

INSERT INTO table_a VALUES
('Mark', 1),
('Mark', 2),
('Martin', 2),
('Jane', 1);

表B（设置表）

CREATE TABLE table_b (
    Possible_Setting INT
);

INSERT INTO table_b VALUES (1), (2), (3);

实现步骤 #

1. 生成所有用户-设置的完整组合

我们需要先得到每个用户和表B中所有设置项的笛卡尔积（也就是每个用户都对应所有可能的设置），这一步用CROSS JOIN就能实现：

SELECT DISTINCT a.username, b.Possible_Setting
FROM table_a a
CROSS JOIN table_b b;

执行后会得到每个用户对应3个设置的完整组合，比如Mark对应1、2、3，Martin对应1、2、3，Jane对应1、2、3。

2. 排除用户已有的设置

接下来从完整组合里去掉表A中已经存在的用户-设置对，这里提供两种常用方法：

方法一：使用LEFT JOIN

SELECT full_combo.username, full_combo.Possible_Setting AS missing_setting
FROM (
    SELECT DISTINCT a.username, b.Possible_Setting
    FROM table_a a
    CROSS JOIN table_b b
) full_combo
LEFT JOIN table_a a
    ON full_combo.username = a.username
    AND full_combo.Possible_Setting = a.setting
WHERE a.setting IS NULL;

方法二：使用NOT EXISTS

SELECT DISTINCT a.username, b.Possible_Setting AS missing_setting
FROM table_a a
CROSS JOIN table_b b
WHERE NOT EXISTS (
    SELECT 1
    FROM table_a a2
    WHERE a2.username = a.username
    AND a2.setting = b.Possible_Setting
);

这两种方法都会得到每个用户缺失的单个设置项，比如Mark的missing_setting是3，Martin的是1、3，Jane的是2、3。

3. 聚合缺失的设置项（可选）

如果希望把每个用户的缺失设置合并成一行展示（用逗号分隔），可以根据你使用的数据库类型，用对应的字符串聚合函数：

• PostgreSQL：用STRING_AGG

  SELECT full_combo.username, STRING_AGG(full_combo.Possible_Setting::TEXT, ', ') AS missing_settings
  FROM (
      SELECT DISTINCT a.username, b.Possible_Setting
      FROM table_a a
      CROSS JOIN table_b b
  ) full_combo
  LEFT JOIN table_a a
      ON full_combo.username = a.username
      AND full_combo.Possible_Setting = a.setting
  WHERE a.setting IS NULL
  GROUP BY full_combo.username;
  

• MySQL：用GROUP_CONCAT

  SELECT full_combo.username, GROUP_CONCAT(full_combo.Possible_Setting SEPARATOR ', ') AS missing_settings
  FROM (
      SELECT DISTINCT a.username, b.Possible_Setting
      FROM table_a a
      CROSS JOIN table_b b
  ) full_combo
  LEFT JOIN table_a a
      ON full_combo.username = a.username
      AND full_combo.Possible_Setting = a.setting
  WHERE a.setting IS NULL
  GROUP BY full_combo.username;
  

• SQL Server：用STRING_AGG

  SELECT full_combo.username, STRING_AGG(CAST(full_combo.Possible_Setting AS VARCHAR), ', ') AS missing_settings
  FROM (
      SELECT DISTINCT a.username, b.Possible_Setting
      FROM table_a a
      CROSS JOIN table_b b
  ) full_combo
  LEFT JOIN table_a a
      ON full_combo.username = a.username
      AND full_combo.Possible_Setting = a.setting
  WHERE a.setting IS NULL
  GROUP BY full_combo.username;
  

最终结果 #

运行聚合查询后，会得到和你期望完全一致的结果：

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