表结构及所有记录:
id score
1 69
2 65
3 73
4 82
5 80问题:我想得到score第四五名的id和score想得到结果集是:id score
2 65
1 69
我的SQL语句:在此基础上怎么改动,谢谢!!!SELECT Score.ID, Score.Score
FROM Score
LEFT OUTER JOIN
(
(
SELECT TOP 3 Score.ID,Score.Score
FROM Score
) AS S1
INNER JOIN
(
SELECT TOP 5 Score.ID,Score.Score
FROM Score
) AS S2
ON S1.ID = S2.ID
) AS S
ON Score.ID = S.ID
WHERE S.ID IS NULL
id score
1 69
2 65
3 73
4 82
5 80问题:我想得到score第四五名的id和score想得到结果集是:id score
2 65
1 69
我的SQL语句:在此基础上怎么改动,谢谢!!!SELECT Score.ID, Score.Score
FROM Score
LEFT OUTER JOIN
(
(
SELECT TOP 3 Score.ID,Score.Score
FROM Score
) AS S1
INNER JOIN
(
SELECT TOP 5 Score.ID,Score.Score
FROM Score
) AS S2
ON S1.ID = S2.ID
) AS S
ON Score.ID = S.ID
WHERE S.ID IS NULL
第一步:排名求学生语文(CHINESE)成绩的名次)(同分的名次一样,后面的名次要跳过一个名次) 即输出的结果declare @t table(stu_id int,stud_name varchar(20),chinese decimal(10,2))
insert @t
select 1,'jfk',77 union all
select 2,'Jk',63.2 union all
select 3,'djfkd',44 union all
select 4,'fddk',63.2 union all
select 5,'fd',75 union all
select 6,'gg',77
select *,chineseorder = (select count(*) from @t where chinese > a.chinese) + 1 from @t a
order by chineseorder--加并列的说明
select *, chineseorder =cast((select count(*) from @t where chinese > a.chinese) +1 as varchar(5)) +
(case when ((select count(*) from @t where chinese=a.chinese) >1) then '-并列' else '' end )
from @t a
order by chineseorder,stu_id
select * from test where 排名=4 or 排名=5
(
select id,score,px=(select count(*) from score where a.score<=score) from score a
) as b
where px in(4,5)
楼上的可以 还可以排序啊 取 top 5