SQL Server queries the first two student numbers, course Numbers, scores, and sorting of each course

Data structure introduction:

Student table (number, student ID, subject number, score)

SQL basics:

Select * from orders table

For SQL Server, the query process is one by one, that is, one record;

Then we can first think about it. When it queries the "orders table", the first query is successful before the second query;

 

Assume that the first two records of the highest score of a specific subject are queried:

Select Top 2 * from orders table where subject number = 1 order by score DESC

 

To query the first two scores of each subject, you can think like this:

When the first row is queried by the SELECT statement, the system will get the (number, student ID, subject number, and score ),

Does this record match the first two conditions for the current score being the highest score of the current subject?

The preceding method is used to obtain the first two methods, so we can proceed here:

Select student ID, subject number, score from student table as

Where student ID in

(

Select Top 2 student ID from student table as B where B. Subject no. = A. Account No.Order by score DESC

)

 

The above script can be understood:

Because the query is one by one, the (yellow area) can be understood as a condition judgment when the first data is obtained;

Whether the student ID exists in the first two of the highest scores in the current record;

The subquery part is the first two student IDs with the highest score of the current record.