如何正确统计每位员工总分及指定时间段内的员工总分？

你的原SQL只统计了Staff1='Sam'的行，但Sam还会出现在Staff2字段里（比如ID=3的记录），而且没处理NULL值（SUM(NULL)会返回NULL，得用COALESCE转成0），所以结果错误。

正确做法是把Staff1和Staff2的记录拆成统一的员工-分数结构，再分组求和：

SELECT 
    staff_name,
    SUM(score) AS total_score
FROM (
    -- 提取Staff1的分数记录
    SELECT 
        Staff1 AS staff_name,
        COALESCE(Staff1_Score, 0) AS score
    FROM your_table_name
    WHERE Staff1 IS NOT NULL
    UNION ALL
    -- 提取Staff2的分数记录
    SELECT 
        Staff2 AS staff_name,
        COALESCE(Staff2_Score, 0) AS score
    FROM your_table_name
    WHERE Staff2 IS NOT NULL
) AS staff_scores
GROUP BY staff_name;

运行后会得到：

• Sam总分：380
• Joe总分：400
  和预期一致。

在上面的逻辑基础上，添加日期范围过滤即可，注意和数据库里的日期格式匹配（这里是DD-MM-YYYY）：

SELECT 
    staff_name,
    SUM(score) AS total_score
FROM (
    SELECT 
        Staff1 AS staff_name,
        COALESCE(Staff1_Score, 0) AS score
    FROM your_date_table_name
    WHERE Staff1 IS NOT NULL
        AND Date BETWEEN '01-05-2023' AND '12-05-2023'
    UNION ALL
    SELECT 
        Staff2 AS staff_name,
        COALESCE(Staff2_Score, 0) AS score
    FROM your_date_table_name
    WHERE Staff2 IS NOT NULL
        AND Date BETWEEN '01-05-2023' AND '12-05-2023'
) AS staff_scores
GROUP BY staff_name;

针对2023年5月1日至12日的时间段，结果为：

• Sam总分：350
• Joe总分：150

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