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

Tags: ,

Recommended Today

Summary of ruby methods for creating arrays

Programs often need to manage variable sets. For example, the program that manages the calendar must have a list of days of the week. They must be stored in a variable every day, and their list can be stored in an array variable. Through this array variable, you can access every day. Create an empty […]