时间戳

首页 > 解决方案 > 复杂的转换和连接不起作用

mysql - 复杂的转换和连接不起作用

问题描述

我对复杂的 MySQL 查询感到困惑。我尝试过连接，并且尝试使用 where X=Y 子句将多个 WordPress 表合并在一起。

我正在尝试做的是导出 WordPress 用户，包括与另一个表中的活动信息合并的特定用户元数据。

如果你不熟悉 WordPress 的表结构，这里总结一下相关的内置表

_usermeta

umeta_id 用户身份 元密钥 元值
1 1 名 凯文
2 1 姓 哎呀
3 1 身份证号码 123456

_users

ID 用户登录 用户通行证 ... 用户电子邮件 ...
1 凯文 ... ... kevin@example.com ...

_posts

ID ... 帖子标题 ...
1 ... 课程名 ...

我们使用的插件是Learn Dash，我们正在尝试从下表中提取活动信息

_learndash_user_activity

活动 ID 用户身份 post_id course_id 活动类型 活动状态 活动开始 活动完成 活动更新
34 5 252 252 课程 1 1612283718 1614975038 1614975038
35 5 271 252 课 1 1612283818 1614976038 1614975034

要将元数据转换为有用的东西，我知道我可以做这样的事情，它可以让我得到用户的姓名、ID 信息和电子邮件地址

SELECT _usermeta.user_id,
MAX(CASE WHEN _usermeta.meta_key = "first_name" THEN _usermeta.meta_value END) "First Name",
MAX(CASE WHEN _usermeta.meta_key = "last_name" THEN _usermeta.meta_value END) "Last Name",
MAX(CASE WHEN _usermeta.meta_key = "ID_number" THEN _usermeta.meta_value END) "ID Number",
_users.user_email as "Email Address"
FROM _usermeta
JOIN _users on _usermeta.user_id = _users.ID
GROUP BY _usermeta.user_id

而且，我可以像这样获取完成的课程信息

SELECT  
_learndash_user_activity.user_id as "User ID", 
_posts.post_title as "Course Name",
FROM_UNIXTIME(_learndash_user_activity.activity_completed) as "Completed Timestamp"      
FROM _learndash_user_activity
JOIN _posts on _learndash_user_activity.post_id = _posts.ID
WHERE 
_learndash_user_activity.activity_type = 'course' AND
_learndash_user_activity.activity_status = 1 AND
_learndash_user_activity.activity_completed IS NOT NULL

它返回一个包含用户 ID 的所有已完成课程的表

但是，当我尝试在 user_id 的另一个连接上将这两个查询合并在一起以将所有内容组合在一起时，我要么超时，因为它永远不会结束，或者我得到一个永远不会结束的数据的垃圾输出。

我的意图是生成这样的表

_输出

_usermeta.user_id _usermeta.meta_key(first_name) _usermeta.meta_key(last_name) _users.user_email _usermeta.meta_key(id_number) _posts.post_title _learndash_user_activity.activity_completed
1 凯文 哎呀 电子邮件地址 123456 课程 101 2021 年 1 月 31 日
2 测试 用户 电子邮件地址 654321 课程 101 2021 年 2 月 1 日
1 凯文 哎呀 电子邮件地址 123456 课程 102 2021 年 2 月 6 日

将这两个查询合并在一起以返回类似于我上面表格的有用信息的最佳方法是什么。这可以通过单个查询来实现吗？

感谢您提供任何建议！

标签： mysqlwordpresswordpress-database

解决方案

你需要加入 bioth 查询

SELECT 
    t1.user_id,
    t1.`First Name`,
    t1.`Last Name`,
    t1.`ID Number`,
    t2.`Course Name`,
    t2.`Completed Timestamp`
FROM
    (SELECT _usermeta.user_id,
    MAX(CASE WHEN _usermeta.meta_key = "first_name" THEN _usermeta.meta_value END) "First Name",
    MAX(CASE WHEN _usermeta.meta_key = "last_name" THEN _usermeta.meta_value END) "Last Name",
    MAX(CASE WHEN _usermeta.meta_key = "ID_number" THEN _usermeta.meta_value END) "ID Number",
    _users.user_email as "Email Address"
    FROM _usermeta
    JOIN _users on _usermeta.user_id = _users.ID
    GROUP BY _usermeta.user_id) t1
JOIN
    (SELECT  
    _learndash_user_activity.user_id as "User ID", 
    _posts.post_title as "Course Name",
    FROM_UNIXTIME(_learndash_user_activity.activity_completed) as "Completed Timestamp"      
    FROM _learndash_user_activity
    JOIN _posts on _learndash_user_activity.post_id = _posts.ID
    WHERE 
    _learndash_user_activity.activity_type = 'course' AND
    _learndash_user_activity.activity_status = 1 AND
    _learndash_user_activity.activity_completed IS NOT NULL) te ON t1.user_id = t2.user_id

推荐阅读