如何合并两个MySQL查询以获取含姓名的课程统计结果？

我需要合并两个MySQL查询，同时获取用户的课程统计数据（总数、已发布数）以及姓名信息（first_name、last_name）：

原查询1：统计用户课程数据 #

该查询用于统计每个用户的课程总数及已发布课程数：

SELECT
wp_users.ID,
wp_users.user_login,
wp_users.user_email,
COUNT(CASE WHEN wp_posts.post_status IN ('draft', 'publish', 'private', 'future', 'pending', 'trash', 'auto-draft') THEN 1 END) AS total_lessons,
COUNT(CASE WHEN wp_posts.post_status = 'publish' THEN 1 END) AS published_lessons
FROM wp_posts, wp_users
WHERE wp_posts.post_type = 'sfwd-courses'
AND wp_posts.post_author = wp_users.ID
GROUP BY wp_users.ID, wp_users.user_nicename
ORDER BY total_lessons DESC, published_lessons DESC;

原查询2：获取用户姓名信息 #

该查询用于获取用户的姓名信息：

SELECT
wp_users.user_login,
wp_users.user_email,
firstmeta.meta_value as first_name,
lastmeta.meta_value as last_name
FROM wp_users
left join wp_usermeta as firstmeta on wp_users.ID = firstmeta.user_id and firstmeta.meta_key = 'first_name'
left join wp_usermeta as lastmeta on wp_users.ID = lastmeta.user_id and lastmeta.meta_key = 'last_name'

合并后的查询 #

将两个查询合并后，即可同时获取所需的所有信息：

SELECT
wp_users.ID,
wp_users.user_login,
wp_users.user_email,
firstmeta.meta_value as first_name,
lastmeta.meta_value as last_name,
COUNT(CASE WHEN wp_posts.post_status IN ('draft', 'publish', 'private', 'future', 'pending', 'trash', 'auto-draft') THEN 1 END) AS total_lessons,
COUNT(CASE WHEN wp_posts.post_status = 'publish' THEN 1 END) AS published_lessons
FROM wp_users
JOIN wp_posts ON wp_posts.post_author = wp_users.ID AND wp_posts.post_type = 'sfwd-courses'
LEFT JOIN wp_usermeta as firstmeta ON wp_users.ID = firstmeta.user_id AND firstmeta.meta_key = 'first_name'
LEFT JOIN wp_usermeta as lastmeta ON wp_users.ID = lastmeta.user_id AND lastmeta.meta_key = 'last_name'
GROUP BY wp_users.ID, wp_users.user_nicename, firstmeta.meta_value, lastmeta.meta_value
ORDER BY total_lessons DESC, published_lessons DESC;

说明： #

1. 将原查询1中的隐式关联改为显式JOIN，逻辑更清晰
2. 加入原查询2中的两次LEFT JOIN，关联wp_usermeta表获取用户姓名
3. 在SELECT列表中新增first_name和last_name字段
4. GROUP BY中补充姓名字段，适配MySQL的分组规则（若开启ONLY_FULL_GROUP_BY模式，必须包含所有非聚合字段）

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