新人请教一个sql成绩排名的问题 求大手子帮忙解答一下

[weixin_41989908]

我有两张表，一张是信息表一张是成绩表

信息（student）表的样子大概是这样
学号 姓名 班级 地区



成绩表(score)是这样的
编号 试卷名称 学号 score1 score2 score3 班级排名 地区排名



怎样写sql语句可以按照班级、地区分别排名并插入到 班级排名和地区排名两列中？

[砸死牛顿的苹果]

score1 score2 score3 加起来算吗

[二月十六]

--测试数据
if not object_id(N'Tempdb..#student') is null
	drop table #student
Go
Create table #student([学号] int,[姓名] nvarchar(22),[班级] nvarchar(22),[地区] nvarchar(22))
Insert #student
select 1,N'张三',N'1班',N'北京' union all
select 2,N'李四',N'1班',N'天津' union all
select 3,N'王五',N'2班',N'北京' union all
select 4,N'赵六',N'2班',N'天津'
GO
if not object_id(N'Tempdb..#score') is null
	drop table #score
Go
Create table #score([编号] int,[试卷名称] nvarchar(23),[学号] int,[score1] int,[score2] int,[score3] int,[班级排名] int,[地区排名] int)
Insert #score
select 1,N'名称1',1,10,20,30,0,0 union all
select 2,N'名称1',2,40,50,60,0,0 union all
select 3,N'名称1',3,40,20,60,0,0 union all
select 4,N'名称1',4,80,90,80,0,0
Go
--测试数据结束
SELECT  #score.编号 ,
        #score.[试卷名称] ,
        #score.学号 ,
        #score.[score1] ,
        #score.[score2] ,
        #score.[score3] ,
        ROW_NUMBER() OVER ( PARTITION BY 班级 ORDER BY score1 + score2 + score3 DESC) AS 班级排名 ,
        ROW_NUMBER() OVER ( PARTITION BY 地区 ORDER BY score1 + score2 + score3 DESC) AS 地区排名
FROM    #student
        JOIN #score ON #score.学号 = #student.学号

[weixin_41989908]

引用 1 楼 ayalicer 的回复:

score1 score2 score3 加起来算吗

对的 算总分忘记说了还有总分字段

[weixin_41989908]

引用 2 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#student') is null
	drop table #student
Go
Create table #student([学号] int,[姓名] nvarchar(22),[班级] nvarchar(22),[地区] nvarchar(22))
Insert #student
select 1,N'张三',N'1班',N'北京' union all
select 2,N'李四',N'1班',N'天津' union all
select 3,N'王五',N'2班',N'北京' union all
select 4,N'赵六',N'2班',N'天津'
GO
if not object_id(N'Tempdb..#score') is null
	drop table #score
Go
Create table #score([编号] int,[试卷名称] nvarchar(23),[学号] int,[score1] int,[score2] int,[score3] int,[班级排名] int,[地区排名] int)
Insert #score
select 1,N'名称1',1,10,20,30,0,0 union all
select 2,N'名称1',2,40,50,60,0,0 union all
select 3,N'名称1',3,40,20,60,0,0 union all
select 4,N'名称1',4,80,90,80,0,0
Go
--测试数据结束
SELECT  #score.编号 ,
        #score.[试卷名称] ,
        #score.学号 ,
        #score.[score1] ,
        #score.[score2] ,
        #score.[score3] ,
        ROW_NUMBER() OVER ( PARTITION BY 班级 ORDER BY score1 + score2 + score3 DESC) AS 班级排名 ,
        ROW_NUMBER() OVER ( PARTITION BY 地区 ORDER BY score1 + score2 + score3 DESC) AS 地区排名
FROM    #student
        JOIN #score ON #score.学号 = #student.学号

这样的话不是创建新的列吗？我是想将排名插入已有的列

[二月十六]

引用 4 楼 weixin_41989908 的回复:

这样的话不是创建新的列吗？我是想将排名插入已有的列

都已经select出来了，更新到旧表中还不会写？

[二月十六]

--测试数据
if not object_id(N'Tempdb..#student') is null
    drop table #student
Go
Create table #student([学号] int,[姓名] nvarchar(22),[班级] nvarchar(22),[地区] nvarchar(22))
Insert #student
select 1,N'张三',N'1班',N'北京' union all
select 2,N'李四',N'1班',N'天津' union all
select 3,N'王五',N'2班',N'北京' union all
select 4,N'赵六',N'2班',N'天津'
GO
if not object_id(N'Tempdb..#score') is null
    drop table #score
Go
Create table #score([编号] int,[试卷名称] nvarchar(23),[学号] int,[score1] int,[score2] int,[score3] int,[班级排名] int,[地区排名] int)
Insert #score
select 1,N'名称1',1,10,20,30,0,0 union all
select 2,N'名称1',2,40,50,60,0,0 union all
select 3,N'名称1',3,40,20,60,0,0 union all
select 4,N'名称1',4,80,90,80,0,0
Go
--测试数据结束
--更新插入
UPDATE #score SET 班级排名=a.班级排名,地区排名=a.地区排名 FROM (
SELECT  #score.编号 ,
        #score.[试卷名称] ,
        #score.学号 ,
        #score.[score1] ,
        #score.[score2] ,
        #score.[score3] ,
        ROW_NUMBER() OVER ( PARTITION BY 班级 ORDER BY score1 + score2 + score3 DESC) AS 班级排名 ,
        ROW_NUMBER() OVER ( PARTITION BY 地区 ORDER BY score1 + score2 + score3 DESC) AS 地区排名
FROM    #student
        JOIN #score ON #score.学号 = #student.学号)a WHERE a.学号=#score.学号
--读取结果
SELECT * FROM #score

[wwfxgm]

引用 6 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#student') is null
    drop table #student
Go
Create table #student([学号] int,[姓名] nvarchar(22),[班级] nvarchar(22),[地区] nvarchar(22))
Insert #student
select 1,N'张三',N'1班',N'北京' union all
select 2,N'李四',N'1班',N'天津' union all
select 3,N'王五',N'2班',N'北京' union all
select 4,N'赵六',N'2班',N'天津'
GO
if not object_id(N'Tempdb..#score') is null
    drop table #score
Go
Create table #score([编号] int,[试卷名称] nvarchar(23),[学号] int,[score1] int,[score2] int,[score3] int,[班级排名] int,[地区排名] int)
Insert #score
select 1,N'名称1',1,10,20,30,0,0 union all
select 2,N'名称1',2,40,50,60,0,0 union all
select 3,N'名称1',3,40,20,60,0,0 union all
select 4,N'名称1',4,80,90,80,0,0
Go
--测试数据结束
--更新插入
UPDATE #score SET 班级排名=a.班级排名,地区排名=a.地区排名 FROM (
SELECT  #score.编号 ,
        #score.[试卷名称] ,
        #score.学号 ,
        #score.[score1] ,
        #score.[score2] ,
        #score.[score3] ,
        ROW_NUMBER() OVER ( PARTITION BY 班级 ORDER BY score1 + score2 + score3 DESC) AS 班级排名 ,
        ROW_NUMBER() OVER ( PARTITION BY 地区 ORDER BY score1 + score2 + score3 DESC) AS 地区排名
FROM    #student
        JOIN #score ON #score.学号 = #student.学号)a WHERE a.学号=#score.学号
--读取结果
SELECT * FROM #score

版主。你的服务真是周到。