mysql - ELSE '60 +'在CASE语句中不起作用，而ELSE'Others'在起作用

下面是一个SQL语句，我用它来查找给定年龄段（百分比）中有多少人。

SELECT
CASE
WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 10 THEN '-10'
WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 20 THEN '11-20'
    WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 30 THEN '21-30'
    WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 60 THEN '31-60'
ELSE '60+'
END AS Age,
(Count(adherent_naissance)* 100 / (Select Count(*) From adherent)) as Pourcentage
FROM adherent
GROUP BY
CASE
WHEN Age <= 10 THEN '-10'
WHEN Age <= 20 THEN '11-20'
    WHEN Age <= 30 THEN '21-30'
    WHEN Age <= 60 THEN '31-60'
ELSE '60+'
END

一切都很好，除了有人属于“其他”类别，然后我有一个奇怪的行为：这个人算在前一个类别。
也就是说我得到了：

Age     Pourcentage
11-20   33.3333
21-30   33.3333
31-60   33.3333

而不是：

Age     Pourcentage
11-20   33.3333
21-30   33.3333
31-60   16.6667
60+     16.6667

ELSE '60'也不起作用，但ELSE 'Others'确实起作用。。。
知道吗？谢谢！
注（测试数据）：SELECT adherent_naissance FROM adherent WHERE 1返回

adherent_naissance
1991-01-09
1990-03-28
1995-09-10
1964-01-10
1992-08-19
1920-02-21

如果有帮助的话，我使用的是MySQL 5.5.24-log。

最佳答案

您需要将相同的CASE表达式添加到GROUP BY子句中

SELECT
CASE
WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 10 THEN '-10'
WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 20 THEN '11-20'
    WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 30 THEN '21-30'
    WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 60 THEN '31-60'
ELSE '60+'
END AS Age,
(Count(adherent_naissance)* 100 / (Select Count(*) From adherent)) as Pourcentage
FROM adherent
GROUP BY
CASE
WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 10 THEN '-10'
WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 20 THEN '11-20'
    WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 30 THEN '21-30'
    WHEN FLOOR(DATEDIFF(NOW(), adherent_naissance)/365.4) <= 60 THEN '31-60'
ELSE '60+'
END

演示SQLFiddle

关于mysql - ELSE '60 +'在CASE语句中不起作用，而ELSE'Others'在起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15876683/