合并WordPress用户元数据与LearnDash活动数据的MySQL Join查询异常

问题产生根源 #

你之前尝试合并两个独立查询时出现重复输出、查询超时的核心原因，是直接将未做粒度收敛的原始表做关联，产生了无意义的笛卡尔积：

• 原始_usermeta表本身是「单用户对应N行元数据」的结构（每个meta_key占独立一行），如果不先通过行转列聚合把每个用户的元数据收敛成1行，直接和学习活动表关联时，会触发「N条用户元数据 × M条用户学习活动记录」的乘法匹配，最终输出N*M条冗余重复数据，同时因为计算量陡增触发查询超时。
• 正确的关联逻辑前提，是先把两个数据块分别收敛到匹配的粒度，再做字段关联。

可直接运行的单查询实现 #

采用「先分块收敛、再关联匹配」的逻辑，单条SQL即可输出符合要求的结果（每个用户的每门已完成课程对应一行，带出全部需要的固定用户信息）：
对于MySQL 8.0及以上支持CTE的版本，代码如下（适配你当前的_表前缀，可直接运行）：

WITH user_base_info AS (
    -- 块1：收敛为「单用户1行」的基础固定信息，减少后续关联计算量
    SELECT
        u.ID AS user_id,
        u.user_email AS "Email Address",
        MAX(CASE WHEN um.meta_key = 'first_name' THEN um.meta_value END) AS "First Name",
        MAX(CASE WHEN um.meta_key = 'last_name' THEN um.meta_value END) AS "Last Name",
        MAX(CASE WHEN um.meta_key = 'ID_number' THEN um.meta_value END) AS "ID Number"
    FROM _users u
    INNER JOIN _usermeta um 
        ON u.ID = um.user_id
    -- 提前过滤需要的元数据键，跳过无关元数据行，大幅降低数据扫描量
    WHERE um.meta_key IN ('first_name', 'last_name', 'ID_number')
    GROUP BY u.ID, u.user_email
),
user_completed_courses AS (
    -- 块2：收敛为「单用户单已完成课程1行」的学习记录
    SELECT
        lla.user_id,
        p.post_title AS "Course Name",
        FROM_UNIXTIME(lla.activity_completed) AS "Completed Timestamp"
    FROM _learndash_user_activity lla
    INNER JOIN _posts p 
        ON lla.post_id = p.ID
    WHERE
        lla.activity_type = 'course'
        AND lla.activity_status = 1
        AND lla.activity_completed IS NOT NULL
)
-- 两个已收敛好的结果集按user_id关联，无笛卡尔积产生
SELECT
    ubi.user_id AS "User ID",
    ubi."First Name",
    ubi."Last Name",
    ubi."ID Number",
    ubi."Email Address",
    ucc."Course Name",
    ucc."Completed Timestamp"
FROM user_base_info ubi
INNER JOIN user_completed_courses ucc
    ON ubi.user_id = ucc.user_id
ORDER BY ubi.user_id, ucc."Completed Timestamp" DESC;

注：如果使用MySQL 5.7及以下不支持CTE的版本，直接将两个WITH块替换为嵌套子查询，逻辑和性能表现完全一致。

灵活适配调整 #

• 如果需要保留所有注册用户（哪怕没有完成任何课程的用户），将最后关联的INNER JOIN改为LEFT JOIN即可，无课程记录的用户对应的课程字段会显示NULL。
• 大流量站点可通过添加联合索引进一步降低查询耗时：
  • 给_usermeta添加(user_id, meta_key, meta_value)联合索引，加快元数据行转列的计算速度
  • 给_learndash_user_activity添加(user_id, activity_type, activity_status, activity_completed, post_id)联合索引，快速筛出符合要求的完成课程记录

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