我有一个这样的表:
Name | Surname | Grade
---------+---------+-------
Alex | Smith | A
Bob | Pratt | B
Sean | Colon | C
Glenda | Huffman | A
Laurence | Bryant | D
Yousuf | Lowry | B
Gracie | Coates | C
Kenneth | Khan | A
我想选择相同年级的学生的姓名、年级和人数,所以结果应该是这样的:
Name | Surname | Grade | Count
---------+---------+-------+------
Alex | Smith | A | 3
Bob | Pratt | B | 2
Sean | Colon | C | 2
Glenda | Huffman | A | 3
Laurence | Bryant | D | 1
Yousuf | Lowry | B | 2
Gracie | Coates | C | 2
Kenneth | Khan | A | 3
我可以在一个查询中完成吗?
你想要一个窗口函数:
select t.*,
count(*) over (partition by grade) as grade_count
from t;
尝试使用OVER子句,详见:https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15
SELECT Name, Surname, Grade, COUNT(Grade) OVER(PARTITION BY Grade) AS Count FROM YourTable
或者你可以使用子选择,但是子选择是性能杀手。
这不是在一个单一的查询,但这里的方法在mysql
SELECT t.*,count FROM vpns_table t
left join (SELECT Grade,count(Grade) count FROM vpns_table group by Grade) t1 on t.Grade=t1.grade