如何在 SQL 中有效地计算列值的出现次数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1503959/
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
How to count occurrences of a column value efficiently in SQL?
提问by Assaf Lavie
I have a table of students:
我有一张学生表：
id | age
--------
0 | 25
1 | 25
2 | 23
I want to query for all students, and an additional column that counts how many students are of the same age:
我想查询所有学生，以及一个计算同龄学生人数的附加列：
id | age | count
----------------
0 | 25 | 2
1 | 25 | 2
2 | 23 | 1
What's the most efficient way of doing this? I fear that a sub-query will be slow, and I'm wondering if there's a better way. Is there?
这样做的最有效方法是什么？我担心子查询会很慢，我想知道是否有更好的方法。在那儿？
回答by Mike Dinescu
This should work:
这应该有效：
SELECT age, count(age)
FROM Students
GROUP by age
If you need the id as well you could include the above as a sub query like so:
如果您还需要 id，您可以将上述内容作为子查询包含在内，如下所示：
SELECT S.id, S.age, C.cnt
FROM Students S
INNER JOIN (SELECT age, count(age) as cnt
FROM Students
GROUP BY age) C ON S.age = C.age
回答by Jeremy Bourque
If you're using Oracle, then a feature called analytics will do the trick. It looks like this:
如果您使用的是 Oracle，那么称为分析的功能就可以解决问题。它看起来像这样：
select id, age, count(*) over (partition by age) from students;
If you aren't using Oracle, then you'll need to join back to the counts:
如果您不使用 Oracle，则需要重新加入计数：
select a.id, a.age, b.age_count
from students a
join (select age, count(*) as age_count
from students
group by age) b
on a.age = b.age
回答by Damian
Here's another solution. this one uses very simple syntax. The first example of the accepted solution did not work on older versions of Microsoft SQL (i.e 2000)
这是另一个解决方案。这个使用非常简单的语法。已接受解决方案的第一个示例不适用于旧版本的 Microsoft SQL（即 2000）
SELECT age, count(*)
FROM Students
GROUP by age
ORDER BY age
回答by quosoo
I would do something like:
我会做这样的事情：
select
A.id, A.age, B.count
from
students A,
(select age, count(*) as count from students group by age) B
where A.age=B.age;
回答by RedFilter
select s.id, s.age, c.count
from students s
inner join (
select age, count(*) as count
from students
group by age
) c on s.age = c.age
order by id
回答by afii_palang
and if data in "age" column has similar records (i.e. many people are 25 years old, many others are 32 and so on), it causes confusion in aligning right count to each student. in order to avoid it, I joined the tables on student ID as well.
如果“年龄”列中的数据有相似的记录（即很多人是 25 岁，很多人是 32 岁等等），就会导致无法将正确计数与每个学生对齐。为了避免这种情况，我也加入了学生证上的表格。
SELECT S.id, S.age, C.cnt
FROM Students S
INNER JOIN (SELECT id, age, count(age) as cnt FROM Students GROUP BY student,age)
C ON S.age = C.age *AND S.id = C.id*
