如何修复数据库状态统计原生查询，将结果正确存入List<Long>

问题原因 #

• COUNT函数逻辑错误：COUNT()统计的是所有非NULL值的数量，原语句中CASE表达式设置了ELSE 0，0属于非NULL值，会被计入统计，导致每一项计数结果都等于符合WHERE条件的总行数，和预期不符。
• GROUP BY配置错误：原语句按原始status字段分组，会将W、I状态拆分为两个独立分组，无法实现合并计数；且需求是全局统计所有符合条件的行，不需要按status分组，直接删除GROUP BY、ORDER BY子句即可。

修改后的查询代码 #

这里使用SUM()替代COUNT()实现计数，逻辑更直观：每行符合条件就加1，不符合加0，求和结果就是对应状态的总数量，返回的List<Long>元素顺序和你预期的D、E、N、S、T、W+I顺序完全对应：

@Query(value = "SELECT " +
        "SUM(CASE WHEN table.status = 'D' THEN 1 ELSE 0 END) AS D," +
        "SUM(CASE WHEN table.status = 'E' THEN 1 ELSE 0 END) AS E," +
        "SUM(CASE WHEN table.status = 'N' THEN 1 ELSE 0 END) AS N," +
        "SUM(CASE WHEN table.status = 'S' THEN 1 ELSE 0 END) AS S," +
        "SUM(CASE WHEN table.status = 'T' THEN 1 ELSE 0 END) AS T," +
        "SUM(CASE WHEN table.status = 'W' OR table.status = 'I' THEN 1 ELSE 0 END) AS WI " +
        "FROM table.table table " +
        "WHERE table.id = :id",
        nativeQuery = true)
List<Long> query1 (Long id);

如果你还是想使用COUNT()实现，只需要把所有CASE的ELSE 0改为ELSE NULL即可，COUNT()遇到NULL值会自动跳过，也能得到正确结果。

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