这是我创建的视图
SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(c.date_of_birth) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(c.date_of_birth, 5)) as age, count(a.id) as occurences, a.celex_id,
COUNT(CASE WHEN c.gender = 'MALE' THEN c.id END) as males,
COUNT(CASE WHEN c.gender = 'FEMALE' THEN c.id END) as females,
COUNT(CASE WHEN c.gender IS NULL THEN c.id END) as unknowns
FROM consumer c, account a
WHERE a.consumer_id = c.id
GROUP BY age, a.celex_id;
这将导致：
age | occurences | celex_id | male | female | unknowns
------------------------------------------------------
16 | 1 | 1 | 0 | 1 | 0
65 | 1 | 1 | 1 | 0 | 0
66 | 1 | 1 | 0 | 1 | 0
69 | 1 | 1 | 0 | 0 | 1
等等。
现在，我用case语句进行了一个查询，但是我不能按cases分组，这是查询
SELECT
CASE
WHEN age BETWEEN 0 AND 10 THEN '0-10'
WHEN age BETWEEN 11 AND 20 THEN '11-20'
WHEN age BETWEEN 21 AND 30 THEN '21-30'
WHEN age BETWEEN 31 AND 40 THEN '31-40'
WHEN age BETWEEN 41 AND 50 THEN '41-50'
WHEN age BETWEEN 51 AND 60 THEN '51-60'
WHEN age BETWEEN 61 AND 70 THEN '61-70'
WHEN age BETWEEN 71 AND 80 THEN '71-80'
WHEN age BETWEEN 81 AND 90 THEN '81-90'
WHEN age BETWEEN 91 AND 100 THEN '91-100'
WHEN age > 100 THEN 'Greater than 100'
ELSE 'Unknown'
END AS age_range, males, females, unknowns
FROM age_gender_occurences
WHERE celex_id = 1
GROUP BY age_range, males, females, unknowns
这给了
age_range | males | females | unknowns
--------------------------------------
11-20 | 0 | 1 | 0
21-30 | 0 | 1 | 0
61-70 | 0 | 1 | 0
61-70 | 1 | 0 | 0
61-70 | 0 | 0 | 1
我尝试直接在GROUPBY子句中复制case语句，但这不起作用。有人能告诉我怎样才能让年龄范围变成一句空话吗（像61-70变成1行而不是3行）
最佳答案
您缺少聚合函数。我会用原始表格来写这个：
SELECT (CASE WHEN age BETWEEN 0 AND 10 THEN '0-10'
WHEN age BETWEEN 11 AND 20 THEN '11-20'
WHEN age BETWEEN 21 AND 30 THEN '21-30'
WHEN age BETWEEN 31 AND 40 THEN '31-40'
WHEN age BETWEEN 41 AND 50 THEN '41-50'
WHEN age BETWEEN 51 AND 60 THEN '51-60'
WHEN age BETWEEN 61 AND 70 THEN '61-70'
WHEN age BETWEEN 71 AND 80 THEN '71-80'
WHEN age BETWEEN 81 AND 90 THEN '81-90'
WHEN age BETWEEN 91 AND 100 THEN '91-100'
WHEN age > 100 THEN 'Greater than 100'
ELSE 'Unknown'
END) AS age_range,
SUM(CASE WHEN c.gender = 'MALE' THEN 1 ELSE 0 END) as males,
SUM(CASE WHEN c.gender = 'FEMALE' THEN 1 ELSE 0 END) as females,
SUM(CASE WHEN c.gender IS NULL THEN 1 ELSE 0 END) as unknowns
FROM consumer c JOIN
account a
ON a.consumer_id = c.id
WHERE celex_id = 1
GROUP BY age_range;
如果你想用视图的形式来写（这看起来有点过头了）：
SELECT (CASE WHEN age BETWEEN 0 AND 10 THEN '0-10'
WHEN age BETWEEN 11 AND 20 THEN '11-20'
WHEN age BETWEEN 21 AND 30 THEN '21-30'
WHEN age BETWEEN 31 AND 40 THEN '31-40'
WHEN age BETWEEN 41 AND 50 THEN '41-50'
WHEN age BETWEEN 51 AND 60 THEN '51-60'
WHEN age BETWEEN 61 AND 70 THEN '61-70'
WHEN age BETWEEN 71 AND 80 THEN '71-80'
WHEN age BETWEEN 81 AND 90 THEN '81-90'
WHEN age BETWEEN 91 AND 100 THEN '91-100'
WHEN age > 100 THEN 'Greater than 100'
ELSE 'Unknown'
END) AS age_range,
SUM(males) as males,
SUM(females) as females,
SUM(unknowns) as unknowns
FROM age_gender_occurences
WHERE celex_id = 1
GROUP BY age_range;
换句话说，你需要再次聚合。事实上，在这种情况下，MySQL将执行两个聚合，这就是为什么我推荐使用原始表的更简单版本。