如何在SQL中条件化实现作者与书籍双维度PIVOT？

问题需求 #

需同时将父实体（作者）和子实体（书籍）作为列头执行PIVOT操作，作者列需包含评分平均值和颜色标记，颜色规则：

• 若作者曾获得'r'（红色），始终显示'r'
• 无'r'则显示'y'（黄色）
• 无颜色值时显示'NA'

数据表结构与样本数据 #

user表（作者信息） #

Aid userName
1   author1
2   author2

books表（书籍信息） #

bid NAME Aid
1    x    1
2    y    1
3    z    2

Location表（地区信息） #

loc_id Loc_name
1     UK
2     USA
3     Europe

UserAssign表（关联评分数据） #

uid Aid bid loc_d color reviews
1    1   1   1     y    12
2    1   2   1     r    14
3    2   3   1     y    11
4    1   1   2     y    10
5    2   3   2     y    112

期望输出 #

Location  author1   x  y  author2       z
UK        r,13     12 14  y,11         11
USA       y,10     10     y,112       112 

解决方案（SQL实现） #

步骤1：预处理作者颜色与地区评分均值 #

先通过子查询计算每个作者在各地区的评分平均值，同时确定作者的最终颜色标记：

WITH author_summary AS (
    SELECT
        ua.loc_d,
        ua.Aid,
        AVG(ua.reviews) AS avg_review,
        CASE 
            WHEN EXISTS (SELECT 1 FROM UserAssign ua2 WHERE ua2.Aid = ua.Aid AND ua2.color = 'r')
            THEN 'r'
            WHEN EXISTS (SELECT 1 FROM UserAssign ua2 WHERE ua2.Aid = ua.Aid AND ua2.color IS NOT NULL)
            THEN 'y'
            ELSE 'NA'
        END AS author_color
    FROM UserAssign ua
    GROUP BY ua.loc_d, ua.Aid
),
-- 步骤2：关联所有表整理基础数据
base_data AS (
    SELECT
        l.Loc_name AS Location,
        us.userName AS author_name,
        b.NAME AS book_name,
        ua.reviews,
        CONCAT(asum.author_color, ',', ROUND(asum.avg_review, 0)) AS author_col_value
    FROM UserAssign ua
    JOIN Location l ON ua.loc_d = l.loc_id
    JOIN user us ON ua.Aid = us.Aid
    JOIN books b ON ua.bid = b.bid
    JOIN author_summary asum ON ua.loc_d = asum.loc_d AND ua.Aid = asum.Aid
)
-- 步骤3：执行双维度PIVOT
SELECT
    Location,
    MAX(CASE WHEN author_name = 'author1' THEN author_col_value END) AS author1,
    MAX(CASE WHEN book_name = 'x' THEN reviews END) AS x,
    MAX(CASE WHEN book_name = 'y' THEN reviews END) AS y,
    MAX(CASE WHEN author_name = 'author2' THEN author_col_value END) AS author2,
    MAX(CASE WHEN book_name = 'z' THEN reviews END) AS z
FROM base_data
GROUP BY Location
ORDER BY Location;

说明 #

1. author_summary CTE：计算每个作者在各地区的评分均值，通过EXISTS判断作者最终颜色，确保只要有一次'r'就标记为'r'。
2. base_data CTE：关联所有表，将作者的颜色+均值拼接成目标格式，同时整理书籍的评分数据。
3. 最终PIVOT：通过MAX(CASE...)实现双维度列转行，分别提取作者的汇总值和对应书籍的评分。

若需适配动态的作者/书籍列表，可使用动态SQL生成CASE语句，避免硬编码列名。

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