这是另一个解决方案。这一个使用非常简单的语法。第一个被接受的解决方案的例子在旧版本的Microsoft SQL(即2000)上不起作用

SELECT age, count(*)
FROM Students 
GROUP by age
ORDER BY age

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

这是另一个解决方案。这一个使用非常简单的语法。第一个被接受的解决方案的例子在旧版本的Microsoft SQL(即2000)上不起作用

SELECT age, count(*)
FROM Students 
GROUP by age
ORDER BY age

如果你使用的是Oracle，那么一个叫做“分析”的功能就可以发挥作用。它是这样的:

select id, age, count(*) over (partition by age) from students;

如果你不使用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

我会这样做:

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;

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