mysql - sql中按状态计数列

Question

我需要 SQL 表中的寄存器总数。
我有这样的状态:

SELECT 
    COUNT(CASE WHEN status = 'WAITING_MEASUREMENT' THEN 1 ELSE NULL END) as '1',
    COUNT(CASE WHEN status = 'WAITING_WEIGHMENT' THEN 1 ELSE NULL END) as '2',
    COUNT(CASE WHEN status = 'WAITING_DATA_ENTRY' THEN 1 ELSE NULL END) as '3',
    COUNT(CASE WHEN status = 'WAITING_PICTURES' THEN 1 ELSE NULL END) as '4'
FROM product;

但是当我运行查询时，我在所有列中都有相同的结果。查询有什么问题？

Answer 1

您应该求和而不是计数，并使用 0 而不是 null，计数只会计算所有记录。

 SELECT 
    SUM(CASE WHEN status = 'WAITING_MEASUREMENT' THEN 1 ELSE 0 END) as '1',
    SUM(CASE WHEN status = 'WAITING_WEIGHMENT' THEN 1 ELSE 0 END) as '2',
    SUM(CASE WHEN status = 'WAITING_DATA_ENTRY' THEN 1 ELSE 0 END) as '3',
    SUM(CASE WHEN status = 'WAITING_PICTURES' THEN 1 ELSE 0 END) as '4'
FROM product;