如何编写SQL查询既是独生子又无同祖父母表亲的Child模型记录

你提供的SQL存在以下问题：

• 仅按祖父母ID分组，最终只返回统计数据，无法返回符合要求的Child完整记录
• HAVING条件完全写反：你需要的是祖父母名下仅1个孙辈，应该使用COUNT(*) = 1而非COUNT("parents.id") > 1
• 关联逻辑可简化：Child和Grandparent的关联可以通过Parent表传递，不需要直接关联，避免数据冗余导致的统计错误

写法1：窗口函数（推荐，性能更优，代码简洁） #

支持MySQL 8.0+/PostgreSQL/SQL Server等主流新版本数据库：

WITH child_with_stat AS (
    SELECT
        c.*,
        -- 统计每个祖父母名下所有孙辈总数
        COUNT(*) OVER (PARTITION BY p.grandparent_id) AS grandchild_total
    FROM childs c
    INNER JOIN parents p ON c.parent_id = p.id
)
-- 过滤出仅属于孙辈总数为1的祖父母的Child记录
SELECT * FROM child_with_stat WHERE grandchild_total = 1;

如果你的Child表直接存储了关联grandparents表的grandparent_id字段，也可以简化写法：

WITH child_with_stat AS (
    SELECT
        c.*,
        COUNT(*) OVER (PARTITION BY c.grandparent_id) AS grandchild_total
    FROM childs c
)
SELECT * FROM child_with_stat WHERE grandchild_total = 1;

写法2：子查询兼容旧版本数据库 #

兼容MySQL 5.x等不支持窗口函数的场景：

SELECT c.*
FROM childs c
INNER JOIN parents p ON c.parent_id = p.id
WHERE p.grandparent_id IN (
    -- 先查询所有仅拥有1个孙辈的祖父母ID
    SELECT p_inner.grandparent_id
    FROM childs c_inner
    INNER JOIN parents p_inner ON c_inner.parent_id = p_inner.id
    GROUP BY p_inner.grandparent_id
    HAVING COUNT(*) = 1
);

满足「祖父母名下仅1个孙辈」的条件时，该孙辈会自动符合你要求的两个规则：

1. 是自己所属Parent的独生子：如果该Parent有多个子女，祖父母的孙辈总数必然≥2，和前提矛盾
2. 无同祖父母的堂/表亲：孙辈总数仅为1，自然不存在其他同辈亲属

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