Self-connection definitions and usage examples in SQL tables

A table is connected to itself, called a self-connected

Question: A netizen put forward such a SQL topic, said oneself thought for a long time did not solve, I see, this is not very simple
But in the Query Analyzer debugging for half a day the original problem is not that simple


There is a student's table, there is a study number
Student score three fields. Use a SQL query to get the top two results for each course

Study number homework number student results
1 1 99
2 1 98
3 1 100
4 2 88
5 2 87
6 2 88
7 3 99
8 3 88
9 3 100

Workaround

SELECT DISTINCTStudent Table 1.* fromStudent Table Student Table 1INNER JOINStudent Table Student table 2 onStudent Table 1. School Numberinch          (SELECT TOP 2Student's Watch, school number. fromStudent TableWHEREStudent tables-Homework numbers=Student Table 1. Homework NumberORDER  byStudent AchievementDESC)

Query results
Study number homework number student results
1 1 99
2 1 98
4 2 88
6 2 88
7 3 99
9 3 100



The same problem is

Find the student's number, course number, and student score of the same students with different course grades.

At the same time: the above SQL statements can also be optimized under:

SELECT Student table 1. *  from WHERE inch (SELECTTOP2fromWHERE=  ORDERbyDESC)  

Self-connection definitions and usage examples in SQL tables