如何用SQL实现保留自身与母亲所有年份/品类组合的表关联转换

要解决这个需求，核心是先构建每个用户的完整Year/Present_category维度集合（包含自身和母亲的所有组合），再通过左关联原表分别统计双方的礼物数量，确保无数据的维度行也能保留并显示0。

完整SQL解决方案 #

-- 提取唯一的用户-母亲关系，避免重复处理
WITH person_info AS (
    SELECT DISTINCT
        Name,
        Mother
    FROM gifts
),
-- 构建每个用户的全维度组合（自身+母亲的Year/Present_category）
all_dimensions AS (
    -- 用户自身的维度数据
    SELECT 
        pi.Name,
        t.Year,
        t.Present_category
    FROM person_info pi
    JOIN gifts t ON pi.Name = t.Name
    
    UNION -- 自动去重，避免同一维度重复出现
    
    -- 用户母亲的维度数据（仅当母亲存在时）
    SELECT 
        pi.Name,
        m.Year,
        m.Present_category
    FROM person_info pi
    JOIN gifts m ON pi.Mother = m.Name
    WHERE pi.Mother IS NOT NULL
)
-- 关联原表统计自身和母亲的礼物数量
SELECT 
    ad.Name,
    ad.Year,
    ad.Present_category,
    -- 自身礼物数：无数据则显示0
    COALESCE(SUM(t.Present_count), 0) AS Present_count_own,
    -- 母亲礼物数：无数据或母亲不存在时显示0
    COALESCE(SUM(m.Present_count), 0) AS Present_count_mother
FROM all_dimensions ad
-- 左关联获取自身的礼物数据
LEFT JOIN gifts t 
    ON ad.Name = t.Name
    AND ad.Year = t.Year
    AND ad.Present_category = t.Present_category
-- 左关联获取母亲的礼物数据
LEFT JOIN gifts m 
    ON (SELECT Mother FROM person_info pi WHERE pi.Name = ad.Name) = m.Name
    AND ad.Year = m.Year
    AND ad.Present_category = m.Present_category
-- 按维度分组统计
GROUP BY ad.Name, ad.Year, ad.Present_category
ORDER BY ad.Name, ad.Year, ad.Present_category;

关键逻辑说明 #

1. person_info CTE：先提取唯一的用户和对应的母亲，避免同一用户的多条记录重复生成维度数据。
2. all_dimensions CTE：通过UNION合并用户自身和母亲的维度组合，自动去重保证每个维度只出现一次。对于无母亲的用户（比如Linda），第二部分查询不会返回数据，因此只保留自身的维度。
3. 左关联与统计：使用LEFT JOIN确保所有维度行都被保留，COALESCE将空值转换为0，解决无数据时的显示问题。
4. 分组聚合：按用户、年份、礼物类别分组，统计各自的礼物总数。

兼容性优化（支持LATERAL JOIN的数据库） #

如果你的数据库支持LATERAL JOIN（如PostgreSQL、SQL Server 2016+），可以用更简洁的方式构建维度集合：

WITH all_dimensions AS (
    SELECT 
        pi.Name,
        d.Year,
        d.Present_category
    FROM (SELECT DISTINCT Name, Mother FROM gifts) pi
    -- 同时获取自身和母亲的维度（母亲不存在时仅返回自身）
    LEFT JOIN LATERAL (
        SELECT Year, Present_category FROM gifts WHERE Name = pi.Name
        UNION
        SELECT Year, Present_category FROM gifts WHERE Name = pi.Mother AND pi.Mother IS NOT NULL
    ) d ON true
)
SELECT 
    ad.Name,
    ad.Year,
    ad.Present_category,
    COALESCE(SUM(t.Present_count), 0) AS Present_count_own,
    COALESCE(SUM(m.Present_count), 0) AS Present_count_mother
FROM all_dimensions ad
LEFT JOIN gifts t 
    ON ad.Name = t.Name
    AND ad.Year = t.Year
    AND ad.Present_category = t.Present_category
LEFT JOIN gifts m 
    ON (SELECT Mother FROM (SELECT DISTINCT Name, Mother FROM gifts) pi WHERE pi.Name = ad.Name) = m.Name
    AND ad.Year = m.Year
    AND ad.Present_category = m.Present_category
GROUP BY ad.Name, ad.Year, ad.Present_category
ORDER BY ad.Name, ad.Year, ad.Present_category;

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