MySQL透视查询求助：用户默认与自定义属性合并展示

前提假设（若你的表结构不同，可对应调整字段名） #

• User表：核心字段 user_id（用户唯一标识）、username（用户名，可选）
• Default_Properties表：prop_key（属性名称，如"theme"、"font_size"）、prop_value（默认属性值）
• Custom_Properties表：user_id（关联用户）、prop_key（自定义属性名）、prop_value（自定义属性值）

实现逻辑 #

1. 先将所有用户与默认属性做笛卡尔积，确保每个用户都拥有全部默认属性；
2. 左连接自定义属性表，匹配用户和对应属性；
3. 使用 COALESCE() 函数优先取自定义属性值，无自定义值则用默认值；
4. 最后通过透视将属性转为列，按用户维度展示。

不同数据库的实现代码 #

1. MySQL/PostgreSQL（使用CASE WHEN手动透视）

SELECT
    u.user_id,
    u.username,
    -- 逐个属性生成列，替换成你的实际属性名
    COALESCE(cp1.prop_value, dp1.prop_value) AS theme,
    COALESCE(cp2.prop_value, dp2.prop_value) AS font_size,
    COALESCE(cp3.prop_value, dp3.prop_value) AS notification_enabled
FROM
    User u
CROSS JOIN Default_Properties dp1
CROSS JOIN Default_Properties dp2
CROSS JOIN Default_Properties dp3
LEFT JOIN Custom_Properties cp1 ON u.user_id = cp1.user_id AND cp1.prop_key = 'theme'
LEFT JOIN Custom_Properties cp2 ON u.user_id = cp2.user_id AND cp2.prop_key = 'font_size'
LEFT JOIN Custom_Properties cp3 ON u.user_id = cp3.user_id AND cp3.prop_key = 'notification_enabled'
WHERE
    dp1.prop_key = 'theme'
    AND dp2.prop_key = 'font_size'
    AND dp3.prop_key = 'notification_enabled'
GROUP BY u.user_id, u.username;

2. SQL Server（使用PIVOT语法）

WITH UserProperties AS (
    SELECT
        u.user_id,
        u.username,
        dp.prop_key,
        COALESCE(cp.prop_value, dp.prop_value) AS prop_value
    FROM
        User u
    CROSS JOIN Default_Properties dp
    LEFT JOIN Custom_Properties cp 
        ON u.user_id = cp.user_id AND dp.prop_key = cp.prop_key
)
SELECT
    user_id,
    username,
    [theme],
    [font_size],
    [notification_enabled]
FROM UserProperties
PIVOT (
    MAX(prop_value)
    FOR prop_key IN ([theme], [font_size], [notification_enabled])
) AS PivotTable;

说明 #

• 如果属性数量较多，MySQL/PostgreSQL的手动透视会比较繁琐，可考虑使用动态SQL生成列；
• COALESCE() 函数的作用是返回第一个非NULL的值，完美实现"自定义属性覆盖默认值"的逻辑；
• 若你的表结构字段名不同，只需替换对应字段即可。

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