22,206
社区成员
发帖
与我相关
我的任务
分享
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
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
--> 生成测试数据表: [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 行受影响)
*/
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 行受影响)
**/
SELECT 学校,年级,班级,学生,
[三科(语数外)分]=[语文]+[数学]+[英语],
[三科(语数外)名次]=rank()over(partition by 学校,年级,班级 order by [语文]+[数学]+[英语]),
总分 = [语文]+[数学]+[英语]+[物理]+[化学],
总分名次=rank()over(partition by 学校,年级,班级 order by [语文]+[数学]+[英语]+[物理]+[化学])
FROM [tb]
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 行受影响)
**/