select t.* from tb t where id in(select top 3 id from tb where class=t.class order by s desc) order by class,s desc
select * from tb t where (select count(1)+1 from tb where class=t.class and s>t.s)<=3
select * from (select *,row_number() over(partition by class order by s desc)as [order] from tb) where [order]<4
SELECT * FROM TB T WHERE CHECKSUM(*) IN (SELECT TOP 3 CHECKSUM(*) FROM TB WHERE CLASS=T.CLASS ORDER BY MARKS DESC)
select t.* from tb t where id in(select top 3 id from tb where class=t.class order by s desc) order by class,s desc
--SQL2005适用 if object_id('tb') is not null drop table tb go create table tb(id int,class int,s int)insert tb select 1,1,100 union all select 2,1,99 union all select 3,1,96 union all select 4,1,90 union all select 5,2,97 union all select 6,2,85 union all select 7,2,93 union all select 8,2,85 union all select 9,3,90 --如果并列都算用RANK(),排除并列用DENSE_RANK() select * from (select *,RANK() OVER(PARTITION BY class ORDER BY s desc) as [Order] FROM tb) a where a.[Order] between 1 and 3
后面几个还需要加上order by class,s desc
--用dense_rank select * from (select *,dense_RANK() OVER(PARTITION BY class ORDER BY s desc) as [Order] FROM tb) a where [Order]<=3
if object_id('tb') is not null drop table tb create table tb(id int,class int,s int)insert tb select 1,1,100 union all select 2,1,99 union all select 3,1,96 union all select 4,1,90 union all select 5,2,97 union all select 6,2,85 union all select 7,2,93 union all select 8,2,85 union all select 9,3,90 select bb.* from (select *,row_number() over (partition by class order by s) rank from tb) bb where bb.rank<=3 order by bb.class,bb.rankid class s rank ----------- ----------- ----------- -------------------- 4 1 90 1 3 1 96 2 2 1 99 3 6 2 85 1 8 2 85 2 7 2 93 3 9 3 90 1(7 行受影响)
select t.* from tb t where s in (select top 3 s from tb where class = t.class order by s desc)
--sql 2005 select id,class,s from ( select t.* , px = row_number() rank(partition class order by s desc) from tb t ) m where px <= 3
from tb t
where (select count(1)+1 from tb where class=t.class and s>t.s)<=3
if object_id('tb') is not null drop table tb
go
create table tb(id int,class int,s int)insert tb
select 1,1,100 union all
select 2,1,99 union all
select 3,1,96 union all
select 4,1,90 union all
select 5,2,97 union all
select 6,2,85 union all
select 7,2,93 union all
select 8,2,85 union all
select 9,3,90
--如果并列都算用RANK(),排除并列用DENSE_RANK()
select *
from (select *,RANK() OVER(PARTITION BY class ORDER BY s desc) as [Order] FROM tb) a
where a.[Order] between 1 and 3
select *
from (select *,dense_RANK() OVER(PARTITION BY class ORDER BY s desc) as [Order] FROM tb) a
where [Order]<=3
create table tb(id int,class int,s int)insert tb
select 1,1,100 union all
select 2,1,99 union all
select 3,1,96 union all
select 4,1,90 union all
select 5,2,97 union all
select 6,2,85 union all
select 7,2,93 union all
select 8,2,85 union all
select 9,3,90
select bb.* from (select *,row_number() over (partition by class order by s) rank from tb) bb
where bb.rank<=3 order by bb.class,bb.rankid class s rank
----------- ----------- ----------- --------------------
4 1 90 1
3 1 96 2
2 1 99 3
6 2 85 1
8 2 85 2
7 2 93 3
9 3 90 1(7 行受影响)
select t.* from tb t where s in (select top 3 s from tb where class = t.class order by s desc)
select id,class,s from
(
select t.* , px = row_number() rank(partition class order by s desc) from tb t
) m
where px <= 3
不过,这些好像是mssql才行的~~
那个mysql如何实现呢?
2楼的代码自己没有看懂,但是,在mysql下是正确的~~~~~
后面呢…………没有细细看了!基础不行!!!
分少,大家多多包涵!