一道面试题，用一条语句选出分数排名第5到第7的学生，最好还要是能写成存储过程，比如从N->M的排名

Question

create proc p_qry
@n int=5,
@m int=7
as
select *
from [table]
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m

Answer 1

--存储过程少写了别名
create proc p_qry
@n int=5,
@m int=7
as
select *
from [table] a
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m

Answer 2

--测试--测试数据
create table [table](id int,score int)
insert [table] select top 20 id,colid
from syscolumns
order by newid()
go--存储过程
create proc p_qry
@n int=5,
@m int=7
as
select *
from [table] a
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m
go--显示原表数据
select * from [table] order by score desc--调用存储过程进行查询
exec p_qry
go--删除测试
drop table [table]
drop proc p_qry/*--测试结果id          score       
----------- ----------- 
2           29
3           25
14          21
14          16
10          13
4           12
12          9
4           6
261575970   6
1           6
261575970   5
1975678086  4
277576027   3
379148396   3
10          3
261575970   2
229575856   2
19          2
9           1
23          1（所影响的行数为 20 行）id          score       
----------- ----------- 
12          9
4           12
10          13（所影响的行数为 3 行）--*/

Answer 3

id          score
----------- -----------
 1662628966          15
         40          15
         41          14
  859866130          10
         31           5
 2095346529           4
  456388695           4
  206623779           3
         33           3
  788913882           3
         38           2
 1746105261           2
  162815642           2
 1588916732           2
  463340715           2
 1977058079           2
 1563868638           1
 1733581214           1
 1700201107           1
  326292222           1得到的结果竟然是：
 ----------- -----------
   456388695           4
   206623779           3
   463340715           2
  1977058079           2
          33           3
  1588916732           2
          38           2
  1746105261           2
   162815642           2
  2095346529           4
   788913882           3显然是 count(distinct score) 造成的。

Answer 4

不会吧..我试了试,为什么不成功....
执行存储过程显示:
    id          score       
----------- ----------- 
1060198827  4
1303675692  2
1664724983  2
1741249258  2
1410104064  2
925246351   2
944722418   3
846626059   2
308196148   2
898102240   2
503672842   4
288720081   2（所影响的行数为 12 行）
          是不是搞错了.......

Answer 5

这个是列出前七个 select top 7 * from table order by 分数
列出前七个中分数最小的最后两个select top 7-5 * from table 
where 主码 in (select top 7 主码 from table order by 分数)
order by 分数 desc把上边的7和5换为参数的sql @max,@min
select top @max-@min * from table 
where 主码 in (select top @max 主码 from table order by 分数)
order by 分数 desc写为存储过程就是
create proc score
@max int,
@min int
as
declare @sql varchar(1000)set @sql='select top @max-@min * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'
GO-------
大概思路如此，没有调试过
也许没有zjcxc(邹建) 的方法好

Answer 6

恕小的愚钝，zjcxc(邹建)大侠的代码我测试通过，但是，我怎么不明白这个sql 语句的原理，还请zjcxc(邹建)兄解释一下，特别where后面的子查询是什么意思！create proc p_qry
@n int=5,
@m int=7
as
select *
from [table] a
where (select count(distinct score) from [table] where score>=a.score) between @n and @m唉，不懂就是不懂啊郁闷ing

Answer 7

liukanghui(刘康辉) ( ) 信誉：99 
ilittlefat(已瘦很多了！) ( ) 信誉：100 
两位的方法都正确

Answer 8

昨天写得有点急有点错
更正一下
set @sql='select top @max-@min * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'
改为
set @sql='select top '+@max-@min+' * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'

Answer 9

还有点错，再改
呵呵
set @sql='select top '+@max-@min+' * from 表 where 主码 in (select top '+@max+'主码 from 表 order by 分数) order by 分数 desc'

Answer 10

select *
from [table] a --外查询
where 
(
select count(distinct score) from [table] where score>=a.score --内查询
) between @n and @mwhere 里面这句（内查询）是查出分数大于当前学生分数（也就是外查询）的条数，
然后在按照这个数据进行排序查找

Answer 11

我和 ilittlefat(已瘦很多了！) 一样,请解释一下
where (select count(distinct score) from [table] where score>=a.score) between @n and @m
好吗？

Answer 12

select top 2 * from 
(select top 5 * from scoreTable order by score)A 
order by score desc

Answer 13

create proc [studer order] 
@N int,@M int
as
begin
select top (@M-@N+1) * from  (   
select top  ((select count(*) from table1)-@N)from  tabel1 order by score desc)  order by score asc
end

Answer 14

top 后面不能带变量，所以还是  zjcxc(邹建) 的对。 zjcxc(邹建)， 能不能说一下为什么 top后面不能带变量？ 是什么机理？

Answer 15

zjcxc(邹建）厉害，我就没有想到他那样的方法

Answer 16

这个可以吗
create proc score
@max int,
@min int
as
declare @sql varchar(1000)set @sql='select top @max-@min * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'
exec @sql
GO

Answer 17

alter proc  p_qry
@n int,
@m int
as
select *
from [table] a
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m order by score desc
go是不是加一个order by 比较明白

Answer 18

create proc p_qry
@n int=5,
@m int=7
as
select *
from [table] a
where (select count(distinct score) from [table] where score>=a.score) 
between @n and @m order by score desc
我也不太了解请谁指点一下

Answer 19

create proc p_qry
@n int=5,
@m int=7
as
select top m-n+1 *
from [table] 
where id not in (select top n-1 id from [table] order by score desc)
order by score desc

Answer 20

班门弄斧一下，我用临时表解决这问题
@n int=5,
@m int=7
SELECT (SELECT COUNT(DISTINCT a)
          FROM table1 a
          WHERE a.score >= b.score) AS no, *
          into #Temp
FROM table1 b 
ORDER BY no  
select * from #Temp where no between @n and @m
drop table #Temp