如何创建带分页的MySQL多表关联语句，计算用户绩效分数并处理空值？

需求梳理 #

• 查询所有用户的基础信息
• 统计用户在Lessons表中totalPay的总和，无相关数据时返回0（别名totalSum）
• 统计用户在Jobs表中的jobsid数量，无相关数据时返回0（别名greens）
• 统计用户在Job offers表中的offersid数量，无相关数据时返回0（别名golds）
• 计算用户得分：当greens和golds都大于0时，user_score为四舍五入后的(greens/golds)*totalSum，否则为0
• 按user_score降序排序并实现分页，最终展示usersid、user_score、name等字段

完整SQL语句 #

SELECT
    u.usersid,
    u.name,
    COALESCE(SUM(l.totalPay), 0) AS totalSum,
    COALESCE(COUNT(j.jobsid), 0) AS greens,
    COALESCE(COUNT(jo.offersid), 0) AS golds,
    CASE
        WHEN COALESCE(COUNT(j.jobsid), 0) > 0 AND COALESCE(COUNT(jo.offersid), 0) > 0
            THEN ROUND( (COUNT(j.jobsid) / COUNT(jo.offersid)) * COALESCE(SUM(l.totalPay), 0) )
        ELSE 0
    END AS user_score
FROM
    Users u
LEFT JOIN Lessons l ON u.usersid = l.usersid
LEFT JOIN Jobs j ON u.usersid = j.usersid
LEFT JOIN `Job offers` jo ON u.usersid = jo.usersid
GROUP BY
    u.usersid, u.name
ORDER BY
    user_score DESC
-- 替换分页参数：page_num为页码，page_size为每页条数，比如第2页每页10条写 LIMIT 10,10
LIMIT (page_num - 1) * page_size, page_size;

关键逻辑说明 #

• 用LEFT JOIN关联所有子表，确保所有用户都能被查询到，哪怕在子表中没有对应数据
• COALESCE函数处理空值：当聚合函数（SUM/COUNT）返回NULL时，自动替换为0
• CASE分支实现得分条件判断：只有当greens和golds同时大于0时才计算得分，否则直接返回0
• GROUP BY按用户ID和姓名分组，保证每个用户只返回一条统计结果
• ORDER BY按得分降序排列，LIMIT替换成实际分页参数即可实现分页

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