如何在 SQL 中有效地计算列值的出现次数? [英] How to count occurrences of a column value efficiently in SQL?

问题描述

我有一张学生表:

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?

推荐答案

这应该有效:

SELECT age, count(age) 
  FROM Students 
 GROUP by age

如果您还需要 id，您可以将上述内容作为子查询包含在内:

If you need the id as well you could include the above as a sub query like so:

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

这篇关于如何在 SQL 中有效地计算列值的出现次数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！