MySQL 计数大小写返回错误计数

Question

我有以下问题:

我有 mysql 表:

continent,  
country,  
city,  
zip,  
street,  
status (int),  
reason (varchar, can be empty or some text),  
... (some other columns, not required for query)  

我想按国家、城市、邮政编码、街道计算每个状态的出现次数，但将状态 int 映射到文本:

SELECT country,city,zip,street, 
   CASE WHEN (status = '2' AND reason <> '') THEN "BLOCKED" 
        WHEN (status = '2' AND reason='') THEN "DISPUTED"  
        WHEN status = '3' THEN 'EXPIRED' 
        WHEN status = '1' THEN 'ACTIVE' ELSE 'UNKNOWN' 
        END as status, 
    count(*) AS count
FROM people
where continent='Europe'
GROUP BY country,city,zip,street,status

我认为问题出在 GROUP BY 中，但不确定 - 它没有返回正确的数据。

SQLFiddle(检查 Paris/Street2 - 它显示 3 DISPUTED，应该显示 1 BLOCKED 和 2 DISPUTED)

感谢您的意见。

Answer 1

我认为 EngineerCoder 想要写:

select country,city,zip,street, status, count(*) 
from
(
SELECT country,city,zip,street, 
   CASE WHEN (status = '2' AND reason <> '') THEN "BLOCKED" 
        WHEN (status = '2' AND reason='') THEN "DISPUTED"  
        WHEN status = '3' THEN 'EXPIRED' 
        WHEN status = '1' THEN 'ACTIVE' ELSE 'UNKNOWN' 
   END as status, 
FROM people
where continent='Europe'
) AS ilv
GROUP BY country,city,zip,street,status