求学生科目名次的SQL语句实现

[GavinLan]

我用动态生成的办法生成了一个SQL语句串@StrSql(很长、很复杂)，然后用
EXEC(@StrSql)
查询出一个表（在存储过程中），得出如下结果集（存在全局临时表里，不妨叫t1）：

XH KCName 语文 数学 英语 总分
200501 单元一 85.0 95.0 90.0 270
200501 单元二 90.0 95.0 95.0 280
200501 单元三 85.0 95.0 95.0 275
200502 单元一 60.0 75.0 70.0 205
200502 单元二 70.0 75.0 75.0 220
200502 单元三 85.0 85.0 95.0 265

然后我想在这个结果集t1的基础上得出每次考试各科(包括总分，用百分比表示，相同分数的排名相同)的名次，例如要返回如下结果集(表t2)：

XH KCName 语文 数学 英语 总分
200501 单元一 100 100 100 100
200501 单元二 100 100 100 100
200501 单元三 100 100 100 100
200502 单元一 50 50 50 50
200502 单元二 50 50 50 50
200502 单元三 100 50 100 50

该如何处理？

这些操作我都是在服务器上的存储过程中处理的。其中，表t2中的100、50等(由于表t1只有两个人，所以才有这样特殊的数字)学生成绩在班级总人数中占的百分比，比如说100%和50%。一条记录表示一次考试各科的排名，包括总分。

[努力偷懒]

mark

[iwl]

看不明白什么意思

[jadesun]

Select XH,KCName,
语文=convert(int,(Select 语文 From t1 where a.KCname=t1.KCName AND a.XH=t1.XH)/(Select MAX(语文) From t1)*100),
数学=convert(int,(Select 数学 From t1 where a.KCname=t1.KCName AND a.XH=t1.XH)/(Select MAX(数学) From t1)*100),
英语=convert(int,(Select 英语 From t1 where a.KCname=t1.KCName AND a.XH=t1.XH)/(Select MAX(英语) From t1)*100),
总分=convert(int,(Select 总分 From t1 where a.KCname=t1.KCName AND a.XH=t1.XH)/(Select MAX(总分) From t1)*100)
From t1 a

[GavinLan]

TO jadesun(裤衩):
你这种算法是不是用 “每个学生每次考试某一科目的成绩 divide 该次考试的最高成绩” 来算的？名次不能这样算吧，应该是“某次考试某科的成绩<=某个学生该次考试该科目的成绩 的人数 divide 学生所在班级总人数”来算吧，
比如说：
200501 单元一 85.0 95.0 90.0 270
这条记录(第一条)
应该从
200501 单元一 85.0 95.0 90.0 270
200502 单元一 50 50 50 50
中比较，班级人数是2，由于在单元一的考试中，语文成绩 <= 200501学生的人数为2，所以200501号学生的语文成绩排名是 2/2 * 100.0％=100%（而200502号的是50％)，同理，数学的是100%, 英语的也是100％...如此等等...

TO iwl() :
t1表是“每个学生每次考试各科的成绩表”，t2表是“每个学生每次考试各科的成绩排名”， 现在的要求是从表t1中得出表t2，而名次的算法类似或等同于上面对jadesun(裤衩)所说的算法。。。

[GavinLan]

怎么没人帮帮忙的？555~~~

[GavinLan]

哇塞，我的贴子在这里晒了N天了，都没人理，555～～～

[wushimiang12]

主要是不明白你的意思！

[zxbyhcsdn]

的确不明白你的意思!!
给你一个我以前写的排名次的Sql 希望能给你点帮助

>一个Sqlserver的表(jfb)如下
bm jf
--------------------
开发部 12
后勤部 12
支持部 10
行政部 9
办公室 9
售后服务部 8
质量部 7

然后要得出部门的名次

部门 积分 名次
----------------------------------
开发部 12 1
后勤部 12 1
支持部 10 2
行政部 9 3
办公室 9 3
售后服务部 8 4
质量部 7 5

答:
select bm as 部门,jf as 积分,
(select count(A.Jf) from (select Distinct Jf from Jfb) A where A.jf >= jfb.jf) as 名次
from jfb

[GavinLan]

To wushimiang12() And zxbyhcsdn(沙子):
你们在哪些地方不明白？一楼是我的问题，在五楼我对名次的方法说明了一下，请把你们对问题不明的地方告诉我，好让我解释给你们听，谢谢。。。

To zxbyhcsdn(沙子):
你的那种方法在计算一次考试一门科目各学生的名次时就行，甚至计算一次考试多门科目各学生的成绩排名都可行，但是在“同一个表”里面，要得出“多次考试”多门科目各学生的成绩排名我就不知道该怎样弄了。如果按照你上面的那种方法做的话，就会又产生一个问题：就是出现多个查询结果(一次考试一个)，不知如何把这些查询结果合成在一个表里面作为总的查询结果返回(用Union是不行的，因为没法确定有多少次考试)。这个问题困扰我很久了，麻烦帮我想想，谢谢!!

PS：1、可以把t1当作是一个物理源表，而t2是想要得到结果的目标表
2、所有这些工作都是在SQLServer的存储过程中实现，t2作为查询该存储过程要返回的结果表。

[GavinLan]

终于解决该问题，可以结贴啦，^_^

[zxbyhcsdn]

--这个就是多门科目多次考试多学生在一个表的情况:

create table jfb
(
bm varchar(32),
kskm varchar(32),
ksqs varchar(32),
kscj int
)

insert into jfb
select '开发部','先进性教育' ,'1期', 12
union all
select '后勤部' ,'先进性教育' ,'1期', 12
union all
select '支持部','先进性教育' ,'2期', 10
union all
select '行政部','先进性教育' ,'2期', 9
union all
select '办公室','先进性教育' ,'2期', 9
union all
select '售后服务部','政治思想' ,'1期', 8
union all
select '质量部' ,'政治思想' ,'1期', 7
union all
select '后勤部' ,'政治思想' ,'1期', 5
union all
select '支持部','政治思想' ,'2期', 12
union all
select '行政部','政治思想' ,'2期', 9


select bm as 部门,kskm as 科目,ksqs as 考试期数,kscj as 积分,
(select count(Distinct kscj) from Jfb A where A.kscj >= jfb.kscj and A.kskm=jfb.kskm) as 科目排名,
(select count(Distinct kscj) from Jfb A where A.kscj >= jfb.kscj and A.kskm=jfb.kskm and A.ksqs=jfb.ksqs) as 科目每期排名
from jfb
order by kskm,ksqs,kscj desc

--//运行结果 :

部门 科目 考试期数 积分 科目排名 科目每期排名
-------------------------------------------------------------------
行政部 先进性教育 2期 9 3 2
办公室 先进性教育 2期 9 3 2
售后服务部 政治思想 1期 8 3 1
质量部 政治思想 1期 7 4 2
后勤部 政治思想 1期 5 5 3
支持部 政治思想 2期 12 1 1
行政部 政治思想 2期 9 2 2