SQL多表关联查询结果拆分冗余 如何优化实现指定输出格式

关联tbl_users、tbl_users_meta、tbl_users_images查询时出现两类异常：

• 同一用户的ip_address、referrer、user_agent元数据拆分为多行，用户基础信息重复冗余
• 无关联用户的图片路径单独成行
  目标是输出同用户属性合并、重复字段单元格留空的结果集。

直接多表JOIN会触发笛卡尔积效应：

1. tbl_users_meta是典型EAV键值对结构，单用户对应3条元数据记录，直接关联必然把单用户信息拆成3行
2. tbl_users_images和用户是一对多关系，和元数据表关联后行数会进一步倍增
3. 未对多表关联后的同组数据做标记，无法实现重复单元格留空的展示逻辑

逻辑分三步：先把EAV结构的元数据行转列合并为单用户单行，再关联用户表和图片表并给同用户的图片打序号，最后判断序号控制重复字段留空：

WITH user_meta_pivot AS (
    -- EAV元数据行转列，每个用户仅返回1行元数据
    SELECT
        user_id,
        MAX(CASE WHEN meta_key = 'ip_address' THEN meta_value END) AS ip_address,
        MAX(CASE WHEN meta_key = 'referrer' THEN meta_value END) AS referrer,
        MAX(CASE WHEN meta_key = 'user_agent' THEN meta_value END) AS user_agent
    FROM tbl_users_meta
    GROUP BY user_id
),
user_combined AS (
    -- 关联三表，给同用户下的图片按顺序编号
    SELECT
        u.user_id,
        u.username,
        u.register_time,
        ump.ip_address,
        ump.referrer,
        ump.user_agent,
        img.image_path,
        ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY img.image_id) AS row_seq
    FROM tbl_users u
    LEFT JOIN user_meta_pivot ump ON u.user_id = ump.user_id
    LEFT JOIN tbl_users_images img ON u.user_id = img.user_id
)
-- 同用户仅第一行展示基础信息和元数据，后续行对应字段留空
SELECT
    IF(row_seq = 1, user_id, NULL) AS user_id,
    IF(row_seq = 1, username, NULL) AS username,
    IF(row_seq = 1, register_time, NULL) AS register_time,
    IF(row_seq = 1, ip_address, NULL) AS ip_address,
    IF(row_seq = 1, referrer, NULL) AS referrer,
    IF(row_seq = 1, user_agent, NULL) AS user_agent,
    image_path
FROM user_combined
ORDER BY user_id, row_seq;

• 全链路使用LEFT JOIN，既不会丢失无元数据/无上传图片的用户记录，也不会出现无归属的图片单独成行的问题
• 如果使用的是MySQL 5.x等不支持窗口函数的版本，可以用用户变量实现ROW_NUMBER()的同组编号效果，核心的元数据行转列、序号判断留空逻辑不需要调整
• 如果需要调整图片排序规则，修改ROW_NUMBER()里的ORDER BY条件即可

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