又一个相似的学生成绩表查询问题，麻烦了！

Question

有如下表格：
学号    姓名    次数    成绩    日期
940001  王平     1       60     2006/5/7
940001  王平     2       70     2006/6/10
940001  王平     3       80     2006/9/22
940001  王平     4       90     2006/10/15
940002  张三     1       61     2006/11/2
940002  张三     2       76     2006/12/10
940002  张三     3       82     2007/1/22
940002  张三     4       92     2007/3/1
940003  刘威     1       82     2007/1/20
940003  刘威     2       82     2007/3/12
940004  赵亮     1       77     2007/1/20
...
要在上面表格中找出每位同学第一次和最后一次的考试分数，以及最后一次和第一次的成绩差的记录
其中：同一学生只有一条记录的，最后分数和第一次分数显示相同的值
结果如下：
姓名 第一次考试时间 第一次考试分数 最后一次考试时间 最后一次考试分数  增长
王平     2006/5/7       60            2006/10/15         90        30     
张三     2006/11/2      61            2007/3/1           92        31     
刘威     2007/1/20      82            2007/3/12          82        0      
赵亮     2007/1/20      77            2007/1/20          77        0      求能够得出这样结果的SQL查询，谢谢各位高手了！！

Answer 1

--创建测试环境
create table #t(学号 int,姓名 varchar(10),次数 int,成绩 int,日期 smalldatetime)--插入测试数据
insert #t(学号,姓名,次数,成绩,日期)
select '940001','王平','1','60','2006/5/7' union all
select '940001','王平','2','70','2006/6/10' union all
select '940001','王平','3','80','2006/9/22' union all
select '940001','王平','4','90','2006/10/15' union all
select '940002','张三','1','61','2006/11/2' union all
select '940002','张三','2','76','2006/12/10' union all
select '940002','张三','3','82','2007/1/22' union all
select '940002','张三','4','92','2007/3/1' union all
select '940003','刘威','1','82','2007/1/20' union all
select '940003','刘威','2','82','2007/3/12' union all
select '940004','赵亮','1','77','2007/1/20'--求解过程
select a.姓名,a.成绩,a.日期,b.成绩,b.日期,b.成绩-a.成绩
from #t a
join #t b on a.学号 = b.学号
where not exists(select 1 from #t where 学号 = a.学号 and a.日期 > 日期)
      and not exists(select 1 from #t where 学号 = b.学号 and b.日期 < 日期)--删除测试环境
drop table #t/*--测试结果
王平 60 2006-05-07 00:00:00 90 2006-10-15 00:00:00 30
张三 61 2006-11-02 00:00:00 92 2007-03-01 00:00:00 31
刘威 82 2007-01-20 00:00:00 82 2007-03-12 00:00:00 0
赵亮 77 2007-01-20 00:00:00 77 2007-01-20 00:00:00 0*/

Answer 2

Create Table tb(学号  Varchar(10),  姓名 Nvarchar(10),   次数 Int,   成绩  Int,  日期 Varchar(10))
Insert Into tb Select '940001',  N'王平',   1,     60,     '2006/5/7'
Union All Select '940001',  N'王平',   2,     70,     '2006/6/10'
Union All Select '940001',  N'王平',   3,     80,     '2006/9/22'
Union All Select '940001',  N'王平',   4,     90,     '2006/10/15'
Union All Select '940002',  N'张三',   1,     61,     '2006/11/2'
Union All Select '940002',  N'张三',   2,     76,     '2006/12/10'
Union All Select '940002',  N'张三',   3,     82,     '2007/1/22'
Union All Select '940002',  N'张三',   4,     92,     '2007/3/1'
Union All Select '940003',  N'刘威',   1,     82,     '2007/1/20'
Union All Select '940003',  N'刘威',   2,     82,     '2007/3/12'
Union All Select '940004',  N'赵亮',   1,     77,     '2007/1/20'
GO
Select 
*, (最后一次考试分数 - 第一次考试分数) As 增长
From
(
Select
TOp 100 Percent
A.姓名,
Max(Case A.次数 When 1 Then 日期 End) As 第一次考试时间,
Max(Case A.次数 When 1 Then 成绩 End) As 第一次考试分数,
Max(Case A.次数 When B.次数 Then 日期 End) As 最后一次考试时间,
Max(Case A.次数 When B.次数 Then 成绩 End) As 最后一次考试分数
From
tb A
Inner Join
(Select 学号, Max(次数) As 次数 From tb Group By 学号) B
On A.学号 = B.学号
Group By A.学号, A.姓名
Order By A.学号
) C
GO
Drop Table tb
--Result
/*
姓名 第一次考试时间 第一次考试分数 最后一次考试时间 最后一次考试分数 增长
王平 2006/5/7 60 2006/10/15 90 30
张三 2006/11/2 61 2007/3/1 92 31
刘威 2007/1/20 82 2007/3/12 82 0
赵亮 2007/1/20 77 2007/1/20 77 0*/

Answer 3

谢谢allen_cn(波波) 的思路，我自己做出来了
SELECT a.学号, a.姓名, a.日期 AS 最后一次考试时间, a.成绩 AS 最后一次考试分数, b.日期 AS 第一次考试时间, b.成绩 AS 第一次考试分数, (a.成绩-b.成绩) AS 进步
FROM 
[select * from 成绩表 as tmp where not exists (select * from 成绩表 where 学号=tmp.学号 and pid>tmp.pid)]. AS a 
LEFT JOIN 
[select * from 成绩表 as temp where not exists (select * from 成绩表 where 学号=temp.学号 and pid<temp.pid)]. AS b 
ON a.学号=b.学号
ORDER BY a.学号;

Answer 4

算了，在你基礎上改SELECT a.学号, a.姓名, a.日期 AS 最后一次考试时间, a.成绩 AS 最后一次考试分数, b.日期 AS 第一次考试时间, b.成绩 AS 第一次考试分数, (a.成绩-b.成绩) AS 进步
FROM 
[select * from 成绩表 as tmp where Year(日期) >= 2007 And not exists (select * from 成绩表 where 学号=tmp.学号 and pid>tmp.pid And Year(日期) >= 2007)]. AS a 
LEFT JOIN 
[select * from 成绩表 as temp where Year(日期) >= 2007 And not exists (select * from 成绩表 where 学号=temp.学号 and pid<temp.pid And Year(日期) >= 2007)]. AS b 
ON a.学号=b.学号
ORDER BY a.学号;

Answer 5

你的語句好象不是MS SQL的，而是ACCESS的.

Answer 6

paoluo,你喜欢张雨生才叫一天到晚游泳的鱼？