MySQL中wp_usermeta表多行转多列并关联多表去重的SQL查询问题

这问题我太熟悉了！你当前的查询之所以出现重复行，是因为你在关联wp_usermeta时，同时匹配了umeta_id=2和umeta_id=3，这会让数据库返回两条记录（对应两个元数据行），而且你把同一个meta_value同时赋值给firstname和lastname，就出现了两行值重复且不符合预期的结果。

下面给你两种可行的解决方案，按需选择：

方案一：使用CASE WHEN + 聚合函数（推荐处理多个元数据字段） #

这种方法不需要多次关联表，适合需要提取多个meta_key的场景，通过条件判断和聚合函数把多行合并为一行：

SELECT 
    wp_users.ID AS wp_id,
    wp_users.user_email AS email,
    wp_users.user_login AS username,
    wp_users.user_registered AS regDate,
    wp_blogs.path AS subname,
    -- 提取firstname
    MAX(CASE WHEN wp_usermeta.meta_key = 'firstname' THEN wp_usermeta.meta_value END) AS firstname,
    -- 提取lastname
    MAX(CASE WHEN wp_usermeta.meta_key = 'lastname' THEN wp_usermeta.meta_value END) AS lastname,
    -- 你可以继续添加其他需要的元数据字段
    MAX(CASE WHEN wp_usermeta.meta_key = 'nickname' THEN wp_usermeta.meta_value END) AS nickname,
    MAX(CASE WHEN wp_usermeta.meta_key = 'description' THEN wp_usermeta.meta_value END) AS description
FROM wp_users
INNER JOIN wp_blogs ON wp_users.ID = wp_blogs.blog_id
LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
-- 按用户和博客的唯一字段分组，确保聚合后返回单行
GROUP BY wp_users.ID, wp_users.user_email, wp_users.user_login, wp_users.user_registered, wp_blogs.path;

原理说明 #

• CASE WHEN会根据meta_key筛选出对应字段的meta_value，不匹配的会返回NULL
• MAX()聚合函数会忽略NULL值，只保留每个用户对应meta_key的有效取值
• GROUP BY子句必须包含所有非聚合的字段，保证每个用户只返回一行结果

方案二：多次LEFT JOIN wp_usermeta表（适合少量元数据字段） #

如果只需要提取少数几个元数据字段，也可以给每个meta_key单独关联一次wp_usermeta表，通过别名区分：

SELECT 
    wp_users.ID AS wp_id,
    wp_users.user_email AS email,
    wp_users.user_login AS username,
    wp_users.user_registered AS regDate,
    wp_blogs.path AS subname,
    um_first.meta_value AS firstname,
    um_last.meta_value AS lastname,
    um_nick.meta_value AS nickname,
    um_desc.meta_value AS description
FROM wp_users
INNER JOIN wp_blogs ON wp_users.ID = wp_blogs.blog_id
-- 关联firstname对应的元数据行
LEFT JOIN wp_usermeta um_first ON wp_users.ID = um_first.user_id AND um_first.meta_key = 'firstname'
-- 关联lastname对应的元数据行
LEFT JOIN wp_usermeta um_last ON wp_users.ID = um_last.user_id AND um_last.meta_key = 'lastname'
-- 关联nickname对应的元数据行
LEFT JOIN wp_usermeta um_nick ON wp_users.ID = um_nick.user_id AND um_nick.meta_key = 'nickname'
-- 关联description对应的元数据行
LEFT JOIN wp_usermeta um_desc ON wp_users.ID = um_desc.user_id AND um_desc.meta_key = 'description';

原理说明 #

• 每个LEFT JOIN只匹配对应meta_key的元数据行，不会产生多余的重复记录
• 通过给wp_usermeta表起不同的别名（um_first、um_last等），可以直接获取每个字段的对应值

这两种方法都能让你得到预期的单行无重复结果，根据你的实际元数据字段数量选择就行～

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