按年龄范围划分的 SQL 组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8762886/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Group by Age Range
提问by Stan
SQL 2005, I have a table with a column 'ages_c', I need to group the records by age ranges. This is the query that I found on this site and it's getting me 90% there but the 'group by' is erroring, *Invalid column name 'age_range'*
SQL 2005，我有一个包含“ages_c”列的表，我需要按年龄范围对记录进行分组。这是我在这个网站上找到的查询，它让我在那里找到了 90%，但“group by”出错了，*无效的列名“age_range”*
select
case
when age_c <18 then 'Under 18'
when age_c between 18 and 24 then '18-24'
when age_c between 25 and 34then '25-34'
END as age_range,
Count(*) as count
from contacts
group by age_range
order by age_range
When I group and order by 'age_c' my result is:
当我按“age_c”分组和排序时，我的结果是：
Under 18 1
18-24 1
18-24 1
25-34 1
What I want is:
我想要的是：
Under 18 1
18-24 2
25-34 1
Thanks.
谢谢。
回答by Joe Stefanelli
Try it this way instead:
试试这种方式：
SELECT SUM(CASE WHEN age_c < 18 THEN 1 ELSE 0 END) AS [Under 18],
SUM(CASE WHEN age_c BETWEEN 18 AND 24 THEN 1 ELSE 0 END) AS [18-24],
SUM(CASE WHEN age_c BETWEEN 25 AND 34 THEN 1 ELSE 0 END) AS [25-34]
FROM contacts
回答by Eric
Group by age_c-- age_range isn't a physical column. More specifically, do this:
Group by age_c-- age_range 不是物理列。更具体地说，这样做：
group by case
when age_c <18 then 'Under 18'
when age_c between 18 and 24 then '18-24'
when age_c between 25 and 34then '25-34'
END
Since age_rangeis an aliased column, the group byis not aware of it at all. Grouping happens before the column set is calculated. The only clause that you can use your aliases in is order by, since that's the only clause that's executed afterthe column set is calculated.
由于age_range是别名列，因此group by根本不知道它。分组发生在计算列集之前。您可以在其中使用别名order by的唯一子句是，因为这是计算列集后执行的唯一子句。
回答by Sheridan Bulger
Is that your actual code you're using? It doesn't look like it because you're missing a space between 34 and then. That code would error in SQL. Mind sharing the actual unmodified query?
那是您正在使用的实际代码吗？它看起来不像，因为您在 34 和 then 之间缺少一个空格。该代码会在 SQL 中出错。介意分享实际的未修改查询吗？
Anyways, you can use a temp table or a nested query.
无论如何，您可以使用临时表或嵌套查询。
SELECT
CASE
WHEN age_c <18 THEN 'Under 18'
WHEN age_c BETWEEN 18 AND 24 THEN '18-24'
WHEN age_c BETWEEN 25 AND 34 THEN '25-34'
END AS age_range,
INTO #TempAges
FROM contacts
ORDER BY age_c
SELECT COUNT(*) FROM #TempAges GROUP BY age_range
Don't forget to drop the temporary table when you're done with it
完成后不要忘记删除临时表
回答by Mithrandir
You can't group by a column you create in the query. You'll have to do it like this:
您不能按您在查询中创建的列进行分组。你必须这样做：
SELECT count(*), * FROM
(
select
case
when age_c <18 then 'Under 18'
when age_c between 18 and 24 then '18-24'
when age_c between 25 and 34then '25-34'
END as age_range
from contacts
) t
group by age_range
order by age_range
or GROUP BY
或 GROUP BY
case
when age_c <18 then 'Under 18'
when age_c between 18 and 24 then '18-24'
when age_c between 25 and 34then '25-34'
END
回答by funnydman
If your database supports FILTER WHEREsyntax then this can be archived in a very elegant way:
如果您的数据库支持FILTER WHERE语法，那么这可以以非常优雅的方式存档：
SELECT COUNT(id) FILTER (WHERE (age < 18)) AS "Under 18",
COUNT(id) FILTER (WHERE (age >= 18 AND age <= 24)) AS "18-24",
COUNT(id) FILTER (WHERE (age >= 25 AND age <= 34)) AS "25-34"
FROM contacts
Or this one if not:
或者这个，如果不是：
SELECT count(CASE WHEN (age < 18) THEN id ELSE null END) AS "Under_18",
count(CASE WHEN (age >= 18 AND age <= 24) THEN id ELSE null END) AS "18-24",
count(CASE WHEN (age >= 25 AND age <= 34) THEN id ELSE null END) AS "25-34"
FROM contacts
