我有一桌学生:
id | age
--------
0 | 25
1 | 25
2 | 23
我想查询所有学生，并增加一个列，统计有多少学生是同龄学生:
id | age | count
----------------
0 | 25 | 2
1 | 25 | 2
2 | 23 | 1
最有效的方法是什么？I fear that a sub-query will be slow, and I'm wondering if there's a better way.有吗？
这应该是有效的:
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