Oracle SQL按人员ID统计每日交易数异常：列显总和而非当日计数

嘿，我一眼就瞅出你这个统计异常的问题根源啦！

你当前用的COUNT(CASE ... ELSE 0 END)逻辑有个致命的小漏洞：COUNT函数会统计所有非NULL的行。你写了ELSE 0，这意味着不管交易是不是对应日期发生的，CASE表达式都会返回一个非NULL的值（要么是交易号，要么是0），所以COUNT会把所有交易都算进去，结果自然变成了所有天数的计数总和。

两种修复方式，任你选： #

方式1：保留COUNT，但去掉ELSE分支（让不满足条件的返回NULL）

当CASE不匹配时，不写ELSE会默认返回NULL，而COUNT会忽略NULL值，这样就能精准统计当天的交易数了：

SELECT 
    pers.prsid,
    COUNT(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Monday' THEN 1 END) AS "Monday",
    COUNT(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Tuesday' THEN 1 END) AS "Tuesday",
    COUNT(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Wednesday' THEN 1 END) AS "Wednesday",
    COUNT(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Thursday' THEN 1 END) AS "Thursday",
    COUNT(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Friday' THEN 1 END) AS "Friday",
    COUNT(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Saturday' THEN 1 END) AS "Saturday",
    COUNT(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Sunday' THEN 1 END) AS "Sunday"
FROM pers
-- 记得补充pers和transaction表的关联条件，比如：JOIN transaction ON pers.prsid = transaction.prsid
GROUP BY pers.prsid;

方式2：改用SUM函数，明确累加符合条件的交易数

这种方式逻辑更直观，符合条件就加1，不符合就加0，SUM会帮你算出当天的总交易数：

SELECT 
    pers.prsid,
    SUM(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Monday' THEN 1 ELSE 0 END) AS "Monday",
    SUM(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Tuesday' THEN 1 ELSE 0 END) AS "Tuesday",
    SUM(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Wednesday' THEN 1 ELSE 0 END) AS "Wednesday",
    SUM(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Thursday' THEN 1 ELSE 0 END) AS "Thursday",
    SUM(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Friday' THEN 1 ELSE 0 END) AS "Friday",
    SUM(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Saturday' THEN 1 ELSE 0 END) AS "Saturday",
    SUM(CASE WHEN TRIM(TO_CHAR(transaction.actdatetime, 'day')) = 'Sunday' THEN 1 ELSE 0 END) AS "Sunday"
FROM pers
-- 补充关联条件
GROUP BY pers.prsid;

额外提醒： #

1. 务必加GROUP BY pers.prsid：你原来的语句用了DISTINCT，这不是正确的分组统计姿势，必须通过GROUP BY按人员ID分组，才能得到每个人每天的交易数。
2. 规避日期格式的语言环境问题：TO_CHAR(..., 'day')返回的星期名称会受数据库NLS_DATE_LANGUAGE参数影响，比如有的环境会返回全大写的MONDAY，建议显式指定语言避免匹配失败：

   TRIM(TO_CHAR(transaction.actdatetime, 'day', 'NLS_DATE_LANGUAGE=ENGLISH')) = 'Monday'
   

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