现在有个表A,包括学生姓名name,3门功课成绩b1,b2,b3,平均成绩c,学生类别type,现在需要一个查询语句查询出按类别和平均成绩的排序,并多出一列1,2,3
比如有6条记录:
name b1 b2 b3 c type
张三 90 90 90 90 1
李四 99 99 99 99 1
王五 80 80 80 80 2
嘿嘿 70 70 70 70 1
哈哈 50 50 50 50 2
呵呵 60 60 60 60 2查询后要得出:
name b1 b2 b3 c type 排名
李四 99 99 99 99 1 1
张三 90 90 90 90 1 2
嘿嘿 70 70 70 70 1 3
王五 80 80 80 80 2 1
呵呵 60 60 60 60 2 2
哈哈 50 50 50 50 2 3这个语句怎么写,谢谢了!
比如有6条记录:
name b1 b2 b3 c type
张三 90 90 90 90 1
李四 99 99 99 99 1
王五 80 80 80 80 2
嘿嘿 70 70 70 70 1
哈哈 50 50 50 50 2
呵呵 60 60 60 60 2查询后要得出:
name b1 b2 b3 c type 排名
李四 99 99 99 99 1 1
张三 90 90 90 90 1 2
嘿嘿 70 70 70 70 1 3
王五 80 80 80 80 2 1
呵呵 60 60 60 60 2 2
哈哈 50 50 50 50 2 3这个语句怎么写,谢谢了!
select * from #t;
drop table #t;
drop table tb
gocreate table tb(name varchar(10),b1 int,b2 int,b3 int,c int,type int)
insert into tb(name,b1,b2,b3,c,type) values('张三', 90, 90, 90, 90, 1)
insert into tb(name,b1,b2,b3,c,type) values('李四', 99, 99, 99, 99, 1)
insert into tb(name,b1,b2,b3,c,type) values('王五', 80, 80, 80, 80, 2)
insert into tb(name,b1,b2,b3,c,type) values('嘿嘿', 70, 70, 70, 70, 1)
insert into tb(name,b1,b2,b3,c,type) values('哈哈', 50, 50, 50, 50, 2)
insert into tb(name,b1,b2,b3,c,type) values('呵呵', 60, 60, 60, 60, 2)select * , 排名=(select count(1) from tb where type=a.type and c>a.c)+1 from tb a order by type ,排名drop table tb/*
name b1 b2 b3 c type 排名
---------- ----------- ----------- ----------- ----------- ----------- -----------
李四 99 99 99 99 1 1
张三 90 90 90 90 1 2
嘿嘿 70 70 70 70 1 3
王五 80 80 80 80 2 1
呵呵 60 60 60 60 2 2
哈哈 50 50 50 50 2 3(所影响的行数为 6 行)
*/
insert into A
select '张三',90,90,90,90,1
union select '李四',99,99,99,99,1
union select '王五',80,80,80,80,2
union select '嘿嘿',70,70,70,70,1
union select '哈哈',50,50,50,50,2
union select '呵呵',60,60,60,60,2select name,b1,b2,b3,c,type,identity(int,1,1) as paimingc into #t from A order by c desc;
select * from #t;
drop table #t;
-----------------------------
李四 99 99 99 99 1 1
张三 90 90 90 90 1 2
王五 80 80 80 80 2 3
嘿嘿 70 70 70 70 1 4
呵呵 60 60 60 60 2 5
哈哈 50 50 50 50 2 6
go
create table A(name varchar(10),b1 int,b2 int,b3 int,c int,type int)
insert into A
select '张三',90,90,90,90,1
union select '李四',99,99,99,99,1
union select '王五',80,80,80,80,2
union select '嘿嘿',70,70,70,70,1
union select '哈哈',50,50,50,50,2
union select '呵呵',60,60,60,60,2select *,(select count(*) from A a1 where a1.type=A.type and a1.c>=A.c) as '排名' from A
order by type,b1 desc,b2 desc,b3 desc
/*
name b1 b2 b3 c type 排名
---------- ----------- ----------- ----------- ----------- ----------- -----------
李四 99 99 99 99 1 1
张三 90 90 90 90 1 2
嘿嘿 70 70 70 70 1 3
王五 80 80 80 80 2 1
呵呵 60 60 60 60 2 2
哈哈 50 50 50 50 2 3(所影响的行数为 6 行)
*/
order by type,c desc
insert @tbl select N'张三', 90, 90, 90, 90, 1 union all select
N'李四', 99, 99 , 99, 99 , 1 union all select
N'王五', 80 , 80, 80, 80, 2 union all select
N'嘿嘿', 70 , 70 , 70 , 70 , 1 union all select
N'哈哈', 50, 50 , 50 , 50, 2 union all select
N'呵呵', 60, 60, 60, 60 , 2select *,RANK() OVER (PARTITION BY [type] order by c desc) as 排名
from @tbl
order by [type],c desc
排名=(select count(1) from tb where type=a.type and (b1+b2+b3+c)>a.b1+a.b2+a.b3+a.c))+1
from tb a
order by type ,排名