请求编写ANSI SQL查询：标记「不完整家庭」成员

没问题，我来帮你搞定这个ANSI SQL查询！先明确核心需求：我们要识别不完整家庭——即至少有一位家庭成员未出现在「部分成员表」中的家庭，然后把这类家庭的所有成员都标记出来，最终生成第三张表。

假设表结构与示例数据 #

首先我先定义两张输入表的结构（你可以根据实际情况调整表名和字段名）：

1. 全量家庭成员表 family_members

CREATE TABLE family_members (
    family_id INT,       -- 家庭唯一标识
    member_name VARCHAR(50), -- 成员姓名
    PRIMARY KEY (family_id, member_name)
);

2. 部分成员表 partial_members

CREATE TABLE partial_members (
    family_id INT,
    member_name VARCHAR(50),
    PRIMARY KEY (family_id, member_name)
);

插入示例测试数据：

-- 全量家庭成员数据
INSERT INTO family_members VALUES
(1, 'Alice'),
(1, 'Bob'),
(1, 'Charlie'),
(2, 'Dave'),
(2, 'Eve'),
(3, 'Frank');

-- 部分成员数据（家庭1缺Charlie，家庭3无成员）
INSERT INTO partial_members VALUES
(1, 'Alice'),
(1, 'Bob'),
(2, 'Dave'),
(2, 'Eve');

核心查询语句 #

下面是符合ANSI SQL标准的查询，会生成带标记的结果集：

WITH incomplete_family_ids AS (
    -- 第一步：找出所有"不完整家庭"的ID
    SELECT DISTINCT fm.family_id
    FROM family_members fm
    LEFT JOIN partial_members pm
        ON fm.family_id = pm.family_id
        AND fm.member_name = pm.member_name
    -- 左连接后，pm字段为NULL的记录就是未出现在部分表的成员，对应的家庭就是不完整家庭
    WHERE pm.member_name IS NULL
)
-- 第二步：给所有家庭成员标记是否属于不完整家庭
SELECT
    fm.family_id,
    fm.member_name,
    -- 如果家庭ID在不完整列表里，标记为"Yes"，否则"No"
    CASE WHEN ifi.family_id IS NOT NULL THEN 'Yes' ELSE 'No' END AS is_incomplete_family
FROM family_members fm
LEFT JOIN incomplete_family_ids ifi
    ON fm.family_id = ifi.family_id;

查询结果示例 #

执行上面的查询后，会得到如下结果：

family_id | member_name | is_incomplete_family
----------|-------------|----------------------
1         | Alice       | Yes
1         | Bob         | Yes
1         | Charlie     | Yes
2         | Dave        | No
2         | Eve         | No
3         | Frank       | Yes

可以看到：

• 家庭1因为有Charlie没出现在部分表，所有成员都标记为Yes
• 家庭2所有成员都在部分表中，标记为No
• 家庭3没有成员出现在部分表，属于不完整家庭，标记为Yes

直接创建第三张表 #

如果需要直接生成第三张表，可以把查询改成CREATE TABLE ... AS的形式：

CREATE TABLE incomplete_family_members AS
WITH incomplete_family_ids AS (
    SELECT DISTINCT fm.family_id
    FROM family_members fm
    LEFT JOIN partial_members pm
        ON fm.family_id = pm.family_id
        AND fm.member_name = pm.member_name
    WHERE pm.member_name IS NULL
)
SELECT
    fm.family_id,
    fm.member_name,
    CASE WHEN ifi.family_id IS NOT NULL THEN 'Yes' ELSE 'No' END AS is_incomplete_family
FROM family_members fm
LEFT JOIN incomplete_family_ids ifi
    ON fm.family_id = ifi.family_id;

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