SQL query the information of the students with the best scores in each subject

Time:2021-12-4

1. Relevant data sheet

Score table
SQL query the information of the students with the best scores in each subject 
[user] table
SQL query the information of the students with the best scores in each subject
The SQL statement is as follows:

Copy codeThe code is as follows:
–Find out the information of the students with the best scores in each subject
–Self connection
–Select top 1 * from score b where b.scorename = ‘Math’ order by b.score desc

SELECT A.ID,U.Name,A.ScoreName,A.Score
FROM Score A,[User]U
WHERE UID IN (SELECT TOP 1 UID
FROM Score B
WHERE B.ScoreName = A.ScoreName
ORDER BY B.Score DESC) AND A.UID=U.ID
ORDER BY A.ScoreName, A.Score DESC

The query results are as follows:
SQL query the information of the students with the best scores in each subject