Only two fields use one SQL statement to query the name, score, and ranking of a student in the table.

Only two fields use one SQL statement to query the name, score, and ranking of a student in the table.

I encountered this problem when I went to the interview yesterday:

There is a two-dimensional table with only two fields, name and score. How can I use an SQL statement to query a student's name, score, and ranking in the table?
I can't figure out the specific implementation at the same time, so I put forward two ideas: one is to join an SQL statement to query its ranking through join, and the other is to use group.
The answer is a bit imaginary. How can we implement it?
Assume that the table name is Course, and the two fields are name and score respectively.

Implementation statement:

SELECT student, score, (select count (*) FROM table WHERE a. score <= score) AS ranking FROM table AS

Method 2:

declare @Course table(name varchar(100),cj int) insert into @Course select 'a',99 union all select 'b',66 union all select 'c',88 select * from ( select (select 1+count(name) from @Course where cj>t.cj) mc,name,cj from @Course t ) tem where name='b'

If there are records with the same score, the goodspeed statement does have some flaws, but it is perfect if I slightly modified it according to his statement.

SELECT name,score, (SELECT COUNT(*)+1 FROM course WHERE a.score<score) AS sort FROM course AS a order by sort

For more information, please visit here

Database Query program. Let's take a look.

Because the ranking is also tied when scores are tied, Feng's SQL needs to be slightly modified, where @ studentname is the name of the student to be queried:
Select name, score, (select count (*) from score where course as t_Temp where t_Temp.score <course. score) + 1) as scoreindex from course where name = @ studentname

SQL Problems

Select *, (select count (1) from (select distinct fs from Course
) T where t. fs> = a. fs) as paiming
From Course
A
Order by score desc, name;