如何在MS Access/MySQL中按多条件筛选生成目标人员数据表

现有一张主表（假设表名为main_table），字段包括姓名name、姓氏surname、性别gender、丈夫姓氏husband's surname（仅女性可能有值）、城市city、地址address。需要在MS Access或MySQL中生成第二张表，包含以下三类人员：

• 所有男性
• 无配偶女性（丈夫姓氏字段为空的女性）
• 丈夫不在列表中的已婚女性：通过丈夫姓氏+地址匹配，若主表中不存在同一地址下姓氏为该丈夫姓氏的男性，就符合条件

输入表数据 #

预期结果表 #

代码实现 #

MySQL 版本 #

直接用CREATE TABLE ... SELECT生成目标表：

CREATE TABLE target_table AS
SELECT *
FROM main_table
WHERE
  -- 所有男性
  gender = 'm'
  OR
  -- 无配偶女性
  (gender = 'f' AND `husband's surname` IS NULL)
  OR
  -- 丈夫不在列表中的已婚女性
  (
    gender = 'f'
    AND `husband's surname` IS NOT NULL
    AND NOT EXISTS (
      SELECT 1
      FROM main_table mt
      WHERE mt.gender = 'm'
        AND mt.surname = `main_table`.`husband's surname`
        AND mt.address = main_table.address
    )
  );

MS Access 版本 #

Access语法需用方括号包裹特殊字段名，两种实现方式：

方式1：直接创建并插入

CREATE TABLE target_table AS
SELECT *
FROM main_table
WHERE
  gender = 'm'
  OR
  (gender = 'f' AND [husband's surname] IS NULL)
  OR
  (
    gender = 'f'
    AND [husband's surname] IS NOT NULL
    AND NOT EXISTS (
      SELECT 1
      FROM main_table mt
      WHERE mt.gender = 'm'
        AND mt.surname = [main_table].[husband's surname]
        AND mt.address = main_table.address
    )
  );

方式2：先建表再插入（适配部分Access版本）

-- 复制空表结构
SELECT * INTO target_table FROM main_table WHERE 1=0;

-- 插入符合条件的数据
INSERT INTO target_table
SELECT *
FROM main_table
WHERE
  gender = 'm'
  OR
  (gender = 'f' AND [husband's surname] IS NULL)
  OR
  (
    gender = 'f'
    AND [husband's surname] IS NOT NULL
    AND NOT EXISTS (
      SELECT 1
      FROM main_table mt
      WHERE mt.gender = 'm'
        AND mt.surname = [main_table].[husband's surname]
        AND mt.address = main_table.address
    )
  );

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