对同一个表查询结果字段合并的问题

Question

我有一个学生成绩表，里面有某个班的学生几次测验的成绩，现在我要对这个班的学生两次的测验名次进行对比（就是要合并成一个表显示）
StudentID Name Class ExamName Score
20140101 蔡婷婷 初一（1）班 第一次测验 80
20140102 邓嘉仪 初一（1）班 第一次测验 95
20140103 高扬 初一（1）班 第一次测验 87
20140201 郭晓琳 初一（2）班 第一次测验 68
20140202 李元 初一（2）班 第一次测验 78
20140203 梁嘉文 初一（2）班 第一次测验 56
20140101 蔡婷婷 初一（1）班 第二次测验 76
20140102 邓嘉仪 初一（1）班 第二次测验 85
20140103 高扬 初一（1）班 第二次测验 79
20140201 郭晓琳 初一（2）班 第二次测验 65
20140202 李元 初一（2）班 第二次测验 80
20140203 梁嘉文 初一（2）班 第二次测验 62
然后查询结果：将第一次测验的成绩查询出在班的名次和第二次查询出在班的名次，然后按相同姓名合并在同一个查询表中StudentID Name Class       ExamName1 Score   mingci1 ExamName      score2 mingci2
20140101 蔡婷婷 初一（1）班 第一次测验 80             3                    第二次测验        76            4
20140102 邓嘉仪 初一（1）班 第一次测验 95             1             第二次测验 85            1
20140103 高扬 初一（1）班 第一次测验 87             2             第二次测验 79            3
20140201 郭晓琳 初一（2）班 第一次测验 68             5             第二次测验 65            5
20140202 李元 初一（2）班 第一次测验 78             4             第二次测验 80            2
20140203 梁嘉文 初一（2）班 第一次测验 56             6             第二次测验 62            6

Answer 1

-- 另一个贴子已回复， 少了一个 order by create table test(
 StudentID varchar(10), 
 Name varchar(10), 
 Class varchar(20), 
 ExamName varchar(10), 
 Score int
)
go
insert into test values
('20140101', '蔡婷婷', '初一（1）班', '第一次测验', 80),
('20140102', '邓嘉仪', '初一（1）班', '第一次测验', 95),
('20140103', '高扬', '初一（1）班', '第一次测验', 87),
('20140201', '郭晓琳', '初一（2）班', '第一次测验', 68),
('20140202', '李元', '初一（2）班', '第一次测验', 78),
('20140203', '梁嘉文', '初一（2）班', '第一次测验', 56),
('20140101', '蔡婷婷', '初一（1）班', '第二次测验', 76),
('20140102', '邓嘉仪', '初一（1）班', '第二次测验', 85),
('20140103', '高扬', '初一（1）班', '第二次测验', 79),
('20140201', '郭晓琳', '初一（2）班', '第二次测验', 65),
('20140202', '李元', '初一（2）班', '第二次测验', 80),
('20140203', '梁嘉文', '初一（2）班', '第二次测验', 62)
go
select StudentID, Name, Class,
    '第一次测验' as ExamName1,
    sum(case when ExamName = '第一次测验' then score else 0 end) score1,
    row_number() over(
     order by sum(case when ExamName = '第一次测验' then score else 0 end) desc
     ) As MingCi1,
    '第二次测验' as ExamName2,
    sum(case when ExamName = '第二次测验' then score else 0 end) score2,
    row_number() over(
     order by sum(case when ExamName = '第二次测验' then score else 0 end) desc 
     ) As MingCi2
from test 
group by StudentID, Name, Class
order by StudentID
go
drop table test 
go

Answer 2

我是用sql server 2000,没有row_number 这个函数啊，那怎么办啊？

Answer 3

select StudentID, Name, Class, 
    '第一次测验' as ExamName1,
    sum(case when ExamName = '第一次测验' then score else 0 end) score1,
   
    
  (select  count(1)+1  from test  where  score>S.score  and ExamName = '第一次测验' and Class='初一(1)班') as  mingci1,    '第二次测验' as ExamName2,
    sum(case when ExamName = '第二次测验' then score else 0 end) score2 
     
from test S 
group by s.StudentID, s.Name, s.Class 
order by s.StudentID
go结果是
服务器: 消息 8120，级别 16，状态 1，行 1
列 'S.Score' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

Answer 4

我用INNER join 搞定了，谢谢你了select a.StudentID,a.name,a.class,a.examID,a.score as score1,(select  count(1)+1  from test  where  score>a.score  and ExamID=1 and Class='高一(01)班')   as  banmingci1,
(select  count(1)+1  from test  where  score>a.score  and ExamID=1 )   as  jimingci1,b.examid as examid2,b.score as score2,  (select  count(1)+1  from test  where  score>b.score  and ExamID=2 and Class='高一(01)班')   as  banmingci2 ,(select  count(1)+1  from test  where  score>b.score  and ExamID=2 )   as  jimingci2 ,(select  count(1)+1  from test  where  score>a.score  and ExamID=1 and Class='高一(01)班') -(select  count(1)+1  from test  where  score>b.score  and ExamID=2 and Class='高一(01)班') 
 as banmingciduibi,
(select  count(1)+1  from test  where  score>a.score  and ExamID=1 )-(select  count(1)+1  from test  where  score>b.score  and ExamID=2 )
as JiMingciDuibi
from 
 (select a.* from Test a where Class='高一(01)班' and ExamID=1 ) a join
 (select a.* from Test a where Class='高一(01)班' and ExamID=2 ) b  on (a.StudentID=b.StudentID) order by a.StudentID

Answer 5

WITH TEMP1 AS (
SELECT * FROM #test 
PIVOT(MAX(SCORE) FOR ExamName IN (第一次测验,第二次测验))AS P)
SELECT StudentID,NAME,CLASS,第一次测验,RANK()OVER(ORDER BY 第一次测验 DESC)AS mingci1,
第二次测验,RANK()OVER(ORDER BY 第二次测验 DESC)AS mingci2 FROM TEMP1/*
StudentID NAME CLASS 第一次测验 mingci1 第二次测验 mingci2
20140102 邓嘉仪 初一（1）班 95 1 85 1
20140202 李元 初一（2）班 78 4 80 2
20140103 高扬 初一（1）班 87 2 79 3
20140101 蔡婷婷 初一（1）班 80 3 76 4
20140201 郭晓琳 初一（2）班 68 5 65 5
20140203 梁嘉文 初一（2）班 56 6 62 6
*/