如何在SQL中有效地计数列值的发生?

我有一张学生表:

id | age
--------
0  | 25
1  | 25
2  | 23

我想查询所有的学生，和一个额外的列，计算有多少学生是相同的年龄:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

最有效的方法是什么?我担心子查询会很慢，我想知道是否有更好的方法。是吗?

当前回答

这应该可以工作:

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

如果你也需要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

2009-10-01 13:30:05

其他回答

这应该可以工作:

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

如果你也需要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

2009-10-01 13:30:05

这应该可以工作:

您可以获取所有row表并计算行。

Select *，count(*) over() from students;

2022-01-30 17:47:56

如果“年龄”列的数据有类似的记录(即许多人是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*

2017-02-23 17:28:49

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

2009-10-01 13:30:40

我会这样做:

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;

2009-10-01 13:31:40

如何在SQL中有效地计数列值的发生?

推荐文章

最新文章

标签