名次计算

Question

需要对学生的成绩进行排名运算，先按总分排名，如果总分相同在按语文排名，最后的名次是递增的，不要并列的名次。

Answer 1

http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?89116

Answer 2

--sql 2000
select t1.* , px = (select count(1) from 
(
  select m.* , n.科目成绩 from (select 学生编号 , sum(科目成绩) 总分 from tb group by 学生编号) m , tb n where m.学生编号 = n.学生编号 and n.科目名称 = 语文
) t2 where t2.总分 > t1.总分 or (t2.总分 = t1.总分 and t2.科目成绩 > t1.科目成绩) 
) from
(
  select m.* , n.科目成绩 from (select 学生编号 , sum(科目成绩) 总分 from tb group by 学生编号) m , tb n where m.学生编号 = n.学生编号 and n.科目名称 = 语文
) t1--sql 2005
select t.* , px = row_number() over(order by 总分 desc 科目成绩 desc) from
(
select m.* , n.科目成绩 from (select 学生编号 , sum(科目成绩) 总分 from tb group by 学生编号) m , tb n where m.学生编号 = n.学生编号 and n.科目名称 = 语文
) t

Answer 3

--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
---------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        3
ff         76.00        4
bb         56.00        5
cc         56.00        5
ff         50.00        6
--*/--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
--------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8
--*/

Answer 4

CREATE TABLE Score
(
 ID VARCHAR(5),
 Score DECIMAL(10,2)
)INSERT INTO SCORE VALUES('101','100')
INSERT INTO SCORE VALUES('101','99.5')
INSERT INTO SCORE VALUES('101','99.5')
INSERT INTO SCORE VALUES('101','80')
INSERT INTO SCORE VALUES('101','80')
INSERT INTO SCORE VALUES('102','99')
INSERT INTO SCORE VALUES('102','95')
INSERT INTO SCORE VALUES('102','90')
INSERT INTO SCORE VALUES('103','85')
INSERT INTO SCORE VALUES('103','88')
INSERT INTO SCORE VALUES('103','70')
SELECT DISTINCT ID,Score,1 AS RANK INTO #DJL_LSB --插入RANK一列值都为1并创建至临时表中，注意需用DISTINCT函数让其字段唯一,使其排名连续
  FROM SCORE 
  ORDER BY ID,Score DESC 
--使用#DJL_LSB和原表Score关联，使用累加逻辑，让#DJL_LSB中的Score大于等于源表SCORE表中的Score时执行累加
SELECT [学号] = A.ID,C.排名,[分数] = A.Score FROM SCORE A
  OUTER APPLY --此函数只支持2005以上数据库
(SELECT SUM(RANK) AS 排名 FROM #DJL_LSB B WHERE A.ID=B.ID AND B.Score >A.Score) C
  WHERE C.排名 = 1 DROP TABLE #DJL_LSB
DROP TABLE SCORE

Answer 5

row_number()和rank()和dense_rank()
这3个函数LZ了解下

Answer 6

我估计这里有你需要的东西:一个项目涉及到的50个Sql语句(整理版)
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html/*
标题：一个项目涉及到的50个Sql语句(整理版)
作者：爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开)
时间：2010-05-10
地点：重庆航天职业学院
说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。
问题及描述：
--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表 
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表 
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表 
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
*/
--创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')
create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))
insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)
go--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--1.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c 
where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score > c.score
--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)
select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a 
left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
where b.score > isnull(c.score,0)--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c 
where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score < c.score
--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a 
left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
where isnull(b.score,0) < c.score--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.S# = b.S#
group by a.S# , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 60 
order by a.S#--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--4.1、查询在sc表存在成绩的学生信息的SQL语句。
select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.S# = b.S#
group by a.S# , a.Sname
having cast(avg(b.score) as decimal(18,2)) < 60 
order by a.S#
--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
select a.S# , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_score
from Student a left join sc b
on a.S# = b.S#
group by a.S# , a.Sname
having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60 
order by a.S#--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--5.1、查询所有有成绩的SQL。
select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a , SC b 
where a.S# = b.S# 
group by a.S#,a.Sname 
order by a.S#
--5.2、查询所有(包括有成绩和无成绩)的SQL。
select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a left join SC b 
on a.S# = b.S# 
group by a.S#,a.Sname 
order by a.S#--6、查询"李"姓老师的数量 
--方法1
select count(Tname) ["李"姓老师的数量] from Teacher where Tname like N'李%'
--方法2
select count(Tname) ["李"姓老师的数量] from Teacher where left(Tname,1) = N'李'
/*
"李"姓老师的数量   
----------- 
1
*/--7、查询学过"张三"老师授课的同学的信息 
select distinct Student.* from Student , SC , Course , Teacher 
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三'
order by Student.S#--8、查询没学过"张三"老师授课的同学的信息 
select m.* from Student m where S# not in (select distinct SC.S# from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三') order by m.S#--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
--方法1
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#
--方法2
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '02' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '01') order by Student.S#
--方法3
select m.* from Student m where S# in
(
  select S# from
  (
    select distinct S# from SC where C# = '01'
    union all
    select distinct S# from SC where C# = '02'
  ) t group by S# having count(1) = 2 
)
order by m.S#--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
--方法1
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and not exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#
--方法2
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and Student.S# not in (Select SC_2.S# from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#--11、查询没有学全所有课程的同学的信息 
--11.1、
select Student.*
from Student , SC 
where Student.S# = SC.S# 
group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course) 
--11.2
select Student.*
from Student left join SC 
on Student.S# = SC.S# 
group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course) --12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
select distinct Student.* from Student , SC where Student.S# = SC.S# and SC.C# in (select C# from SC where S# = '01') and Student.S# <> '01'--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
select Student.* from Student where S# in
(select distinct SC.S# from SC where S# <> '01' and SC.C# in (select distinct C# from SC where S# = '01') 
group by SC.S# having count(1) = (select count(1) from SC where S#='01')) --14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
select student.* from student where student.S# not in 
(select distinct sc.S# from sc , course , teacher where sc.C# = course.C# and course.T# = teacher.T# and teacher.tname = N'张三')
order by student.S#--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
select student.S# , student.sname , cast(avg(score) as decimal(18,2)) avg_score from student , sc 
where student.S# = SC.S# and student.S# in (select S# from SC where score < 60 group by S# having count(1) >= 2)
group by student.S# , student.sname--16、检索"01"课程分数小于60，按分数降序排列的学生信息
select student.* , sc.C# , sc.score from student , sc 
where student.S# = SC.S# and sc.score < 60 and sc.C# = '01'
order by sc.score desc  --17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--17.1 SQL 2000 静态 
select a.S# 学生编号 , a.Sname 学生姓名 ,
       max(case c.Cname when N'语文' then b.score else null end) [语文],
       max(case c.Cname when N'数学' then b.score else null end) [数学],
       max(case c.Cname when N'英语' then b.score else null end) [英语],
       cast(avg(b.score) as decimal(18,2)) 平均分
from Student a 
left join SC b on a.S# = b.S#
left join Course c on b.C# = c.C#
group by a.S# , a.Sname
order by 平均分 desc
--17.2 SQL 2000 动态 
declare @sql nvarchar(4000)
set @sql = 'select a.S# ' + N'学生编号' + ' , a.Sname ' + N'学生姓名'
select @sql = @sql + ',max(case c.Cname when N'''+Cname+''' then b.score else null end) ['+Cname+']'
from (select distinct Cname from Course) as t
set @sql = @sql + ' , cast(avg(b.score) as decimal(18,2)) ' + N'平均分' + ' from Student a left join SC b on a.S# = b.S# left join Course c on b.C# = c.C#
group by a.S# , a.Sname order by ' + N'平均分' + ' desc'
exec(@sql)
--17.3 有关sql 2005的动静态写法参见我的文章《普通行列转换(version 2.0)》或《普通行列转换(version 3.0)》。