**求一条关于排名的查询语句**

dqk1985 2011-06-02 10:20:17
小弟现在做一个关于成绩软件的名次排名问题

实现的要求是查询效率一定要高;

如下表所示

学校 年级 班级 学生 语文 数学 英语 物理 化学
一中 一年级 一班 1 100 80 70 100 100
一中 一年级 一班 2 90 90 70 110 80
一中 一年级 一班 3 80 90 40 110 90
一中 一年级 一班 4 100 70 80 120 50
一中 一年级 二班 5 100 80 70 100 100
一中 一年级 二班 6 90 90 70 110 80
一中 一年级 二班 7 80 90 40 110 90
一中 一年级 二班 8 100 70 80 120 50

二中 一年级 一班 11 100 80 70 100 100
二中 一年级 一班 12 90 90 70 110 80
二中 一年级 一班 13 80 90 40 110 90
二中 一年级 一班 14 100 70 80 120 50
二中 一年级 二班 15 100 80 70 100 100
二中 一年级 二班 16 90 90 70 110 80
二中 一年级 二班 17 80 90 40 110 90
二中 一年级 二班 18 100 70 80 120 50


要求查询出

学校 年级 班级 学生 三科(语数外)分 三科(语数外)名次 总分 总分名次



注明:如果分数是

300
290
290
280

那么名次就是
1
2
2
4

...全文
49 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
-晴天 2011-06-02
  • 打赏
  • 举报
回复
create table tb(学校 nvarchar(10),年级 nvarchar(10),班级 nvarchar(10),学生 int,语文 int,数学 int,英语 int,物理 int,化学 int)
insert into tb select '一中','一年级','一班',1,100,80,70,100,100
insert into tb select '一中','一年级','一班',2,90,90,70,110,80
insert into tb select '一中','一年级','一班',3,80,90,40,110,90
insert into tb select '一中','一年级','一班',4,100,70,80,120,50
insert into tb select '一中','一年级','二班',5,100,80,70,100,100
insert into tb select '一中','一年级','二班',6,90,90,70,110,80
insert into tb select '一中','一年级','二班',7,80,90,40,110,90
insert into tb select '一中','一年级','二班',8,100,70,80,120,50
insert into tb select '二中','一年级','一班',11,100,80,70,100,100
insert into tb select '二中','一年级','一班',12,90,90,70,110,80
insert into tb select '二中','一年级','一班',13,80,90,40,110,90
insert into tb select '二中','一年级','一班',14,100,70,80,120,50
insert into tb select '二中','一年级','二班',15,100,80,70,100,100
insert into tb select '二中','一年级','二班',16,90,90,70,110,80
insert into tb select '二中','一年级','二班',17,80,90,40,110,90
insert into tb select '二中','一年级','二班',18,100,70,80,120,50
go

select 学校,年级,学生,班级,rank()over(partition by 班级 order by 语文+数学+英语 desc)语数外排名,语文+数学+英语 语数外,
rank()over(partition by 班级 order by 语文+数学+英语+物理+化学 desc)总分排名,语文+数学+英语+物理+化学 总分
from tb
order by 班级,总分排名
/*
学校 年级 学生 班级 语数外排名 语数外 总分排名 总分
---------- ---------- ----------- ---------- -------------------- ----------- -------------------- -----------
二中 一年级 15 二班 1 250 1 450
一中 一年级 5 二班 1 250 1 450
一中 一年级 6 二班 1 250 3 440
二中 一年级 16 二班 1 250 3 440
二中 一年级 18 二班 1 250 5 420
一中 一年级 8 二班 1 250 5 420
二中 一年级 17 二班 7 210 7 410
一中 一年级 7 二班 7 210 7 410
一中 一年级 1 一班 1 250 1 450
二中 一年级 11 一班 1 250 1 450
二中 一年级 12 一班 1 250 3 440
一中 一年级 2 一班 1 250 3 440
一中 一年级 4 一班 1 250 5 420
二中 一年级 14 一班 1 250 5 420
二中 一年级 13 一班 7 210 7 410
一中 一年级 3 一班 7 210 7 410

(16 行受影响)

*/
go
drop table tb
dearbinge 2011-06-02
  • 打赏
  • 举报
回复

with cte as
(
select
学校,年级,班级,学生,
(语文 + 数学 + 英语) as thscore,
(语文 + 数学 + 英语 + 物理 + 化学) as fiscore
from tb
)
select 学校,年级,班级,学生,thscore,dense_rank() over(order by thscore) as thdense,
fiscore,dense_rank() over(order by fiscore) as fidense
from cte
htl258_Tony 2011-06-02
  • 打赏
  • 举报
回复
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([学校] [nvarchar](10),[年级] [nvarchar](10),[班级] [nvarchar](10),[学生] [int],[语文] [int],[数学] [int],[英语] [int],[物理] [int],[化学] [int])
INSERT INTO [tb]
SELECT '一中','一年级','一班','1','100','80','70','100','100' UNION ALL
SELECT '一中','一年级','一班','2','90','90','70','110','80' UNION ALL
SELECT '一中','一年级','一班','3','80','90','40','110','90' UNION ALL
SELECT '一中','一年级','一班','4','100','70','80','120','50' UNION ALL
SELECT '一中','一年级','二班','5','100','80','70','100','100' UNION ALL
SELECT '一中','一年级','二班','6','90','90','70','110','80' UNION ALL
SELECT '一中','一年级','二班','7','80','90','40','110','90' UNION ALL
SELECT '一中','一年级','二班','8','100','70','80','120','50' UNION ALL
SELECT '二中','一年级','一班','11','100','80','70','100','100' UNION ALL
SELECT '二中','一年级','一班','12','90','90','70','110','80' UNION ALL
SELECT '二中','一年级','一班','13','80','90','40','110','90' UNION ALL
SELECT '二中','一年级','一班','14','100','70','80','120','50' UNION ALL
SELECT '二中','一年级','二班','15','100','80','70','100','100' UNION ALL
SELECT '二中','一年级','二班','16','90','70','70','110','80' UNION ALL
SELECT '二中','一年级','二班','17','80','90','40','110','90' UNION ALL
SELECT '二中','一年级','二班','18','100','70','80','120','50'


-->SQL查询如下:
SELECT 学校,年级,班级,学生,
[三科(语数外)分]=[语文]+[数学]+[英语],
[三科(语数外)名次]=rank()over(partition by 学校,年级,班级 order by [语文]+[数学]+[英语]),
总分 = [语文]+[数学]+[英语]+[物理]+[化学],
总分名次=rank()over(partition by 学校,年级,班级 order by [语文]+[数学]+[英语]+[物理]+[化学])
FROM [tb]
/*
学校 年级 班级 学生 三科(语数外)分 三科(语数外)名次 总分 总分名次
---------- ---------- ---------- ----------- ----------- -------------------- ----------- --------------------
二中 一年级 二班 17 210 1 410 1
二中 一年级 二班 16 230 2 420 2
二中 一年级 二班 18 250 3 420 2
二中 一年级 二班 15 250 3 450 4
二中 一年级 一班 13 210 1 410 1
二中 一年级 一班 14 250 2 420 2
二中 一年级 一班 12 250 2 440 3
二中 一年级 一班 11 250 2 450 4
一中 一年级 二班 7 210 1 410 1
一中 一年级 二班 8 250 2 420 2
一中 一年级 二班 6 250 2 440 3
一中 一年级 二班 5 250 2 450 4
一中 一年级 一班 3 210 1 410 1
一中 一年级 一班 4 250 2 420 2
一中 一年级 一班 2 250 2 440 3
一中 一年级 一班 1 250 2 450 4

(16 行受影响)
*/
FlySQL 2011-06-02
  • 打赏
  • 举报
回复
1楼未考虑并列的情况,修正如下:
SELECT 学校,年级,班级,学生,
[三科(语数外)分]=语文+数学+英语,
[三科(语数外)名次]=RANK() over(order by 语文+数学+英语 desc),
总分=语文+数学+英语+物理+化学,
总分名次=RANK() over(order by 语文+数学+英语+物理+化学 desc)
FROM TB
/**
学校 年级 班级 学生 三科(语数外)分 三科(语数外)名次 总分 总分名次
---- ------ ---- ----------- ----------- -------------------- ----------- --------------------
一中 一年级 二班 5 250 1 450 1
二中 一年级 一班 11 250 1 450 1
一中 一年级 一班 1 250 1 450 1
二中 一年级 二班 15 250 1 450 1
二中 一年级 二班 16 250 1 440 5
一中 一年级 一班 2 250 1 440 5
二中 一年级 一班 12 250 1 440 5
一中 一年级 二班 6 250 1 440 5
一中 一年级 二班 8 250 1 420 9
二中 一年级 一班 14 250 1 420 9
二中 一年级 二班 18 250 1 420 9
一中 一年级 一班 4 250 1 420 9
一中 一年级 一班 3 210 13 410 13
二中 一年级 二班 17 210 13 410 13
二中 一年级 一班 13 210 13 410 13
一中 一年级 二班 7 210 13 410 13

(16 行受影响)

**/
东那个升 2011-06-02
  • 打赏
  • 举报
回复
用RANK() OVER()排名函数就可以了
htl258_Tony 2011-06-02
  • 打赏
  • 举报
回复
SELECT 学校,年级,班级,学生,
[三科(语数外)分]=[语文]+[数学]+[英语],
[三科(语数外)名次]=rank()over(partition by 学校,年级,班级 order by [语文]+[数学]+[英语]),
总分 = [语文]+[数学]+[英语]+[物理]+[化学],
总分名次=rank()over(partition by 学校,年级,班级 order by [语文]+[数学]+[英语]+[物理]+[化学])
FROM [tb]
FlySQL 2011-06-02
  • 打赏
  • 举报
回复
SELECT 学校,年级,班级,学生,
[三科(语数外)分]=语文+数学+英语,
[三科(语数外)名次]=row_number() over(order by 语文+数学+英语 desc),
总分=语文+数学+英语+物理+化学,
总分名次=row_number() over(order by 语文+数学+英语+物理+化学 desc)
FROM TB
/**
学校 年级 班级 学生 三科(语数外)分 三科(语数外)名次 总分 总分名次
---- ------ ---- ----------- ----------- -------------------- ----------- --------------------
一中 一年级 二班 5 250 2 450 1
二中 一年级 一班 11 250 5 450 2
一中 一年级 一班 1 250 10 450 3
二中 一年级 二班 15 250 8 450 4
二中 一年级 二班 16 250 9 440 5
一中 一年级 一班 2 250 11 440 6
二中 一年级 一班 12 250 6 440 7
一中 一年级 二班 6 250 3 440 8
一中 一年级 二班 8 250 4 420 9
二中 一年级 一班 14 250 7 420 10
二中 一年级 二班 18 250 12 420 11
一中 一年级 一班 4 250 1 420 12
一中 一年级 一班 3 210 13 410 13
二中 一年级 二班 17 210 14 410 14
二中 一年级 一班 13 210 15 410 15
一中 一年级 二班 7 210 16 410 16

(16 行受影响)

**/

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧