sql查询中对数据处理问题

Question

字段
StudentID int
TestDate datetime
TestResult numeric
想要查询每个学生最后一次测试相比第一次测试成绩的提升比例
StudentID  TestDate TestResult
1          2013-1-3    12
2          2013-1-5    34
1          2013-1-2    23
2          2013-1-7    65
3          2013-1-9    45
1          2013-1-10   78
有比较简便的方式没有，我自己想的都特别麻烦

Answer 1

希望的结果
StudentID  Value
1          （78-12）/12
2           （65-34）/34
3           （45-45）/45

Answer 2

with tb(StudentID,TestDate,TestResult)as(
select 1,'2013-1-3',12 union
select 2,'2013-1-5',34 union
select 1,'2013-1-2',23 union
select 2,'2013-1-7',65 union
select 3,'2013-1-9',45 union
select 1,'2013-1-10',78)
select *,(testresult-(select  min(TestResult) from tb where a.StudentID=StudentID
and convert(date,a.TestDate)>convert(date,TestDate)))*1.00/(select  min(TestResult) 
from tb where a.StudentID=StudentID
and convert(date,a.TestDate)>convert(date,TestDate)) from tb a
order by studentid,convert(date,testdate)

Answer 3

主要是聚合函数的结果要用在where里才麻烦

Answer 4

with tb(StudentID,TestDate,TestResult)as(
select 1,'2013-1-3',12 union
select 2,'2013-1-5',34 union
select 1,'2013-1-2',23 union
select 2,'2013-1-7',65 union
select 3,'2013-1-9',45 union
select 1,'2013-1-10',78)SELECT     StudentID,
 (MAX(CASE WHEN [Type] = 'Max' THEN cast(TestResult as float) ELSE NULL END)- 
                      MAX(CASE WHEN [Type] = 'Min' THEN cast(TestResult as float) ELSE NULL END))/MAX(CASE WHEN [Type] = 'Max' THEN cast(TestResult as float) ELSE NULL END)FROM         (
SELECT    *,'Max' as Type
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY StudentID
                      ORDER BY convert(date,TestDate) DESC) LEV, tb.*
FROM         tb) t
WHERE     t .LEV = 1
union
SELECT    *,'Min' as Type
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY StudentID
                      ORDER BY convert(date,TestDate)) LEV, tb.*
FROM         tb) t
WHERE     t .LEV = 1) AS b
GROUP BY StudentID我的太麻烦

Answer 5

with tb(StudentID,TestDate,TestResult)as(
select 1,'2013-1-3',12 union
select 2,'2013-1-5',34 union
select 1,'2013-1-2',23 union
select 2,'2013-1-7',65 union
select 3,'2013-1-9',45 union
select 1,'2013-1-10',78)
 
SELECT StudentID,第一次成绩,最后一次成绩,(CONVERT(FLOAT,最后一次成绩)-CONVERT(FLOAT,第一次成绩))/CONVERT(FLOAT,第一次成绩) 
FROM
(SELECT  a.StudentID,(SELECT TOP 1 b.TestResult FROM tb b WHERE b.StudentID=a.StudentID ORDER BY CONVERT( DATETIME,b.TestDate)) AS 第一次成绩,(SELECT TOP 1 b.TestResult FROM tb b WHERE b.StudentID=a.StudentID ORDER BY CONVERT( DATETIME,b.TestDate) desc) AS 最后一次成绩
FROM tb a GROUP BY a.StudentID) c

Answer 6

Create table #t(id int, dt datetime, score int);
Insert Into #t 
select 1 as id,'2013-1-3' as dt,12 as score union All
select 2,'2013-1-5',34 union All
select 1,'2013-1-2',23 union All
select 2,'2013-1-7',65 union All
select 3,'2013-1-9',45 union All
select 1,'2013-1-10',78
---------------------------
with t as 
(Select *,ROW_NUMBER() Over(partition by id order by dt ) as rn,
ROW_NUMBER() Over(PArtition by id order by dt desc) as descrn
From #t)
Select id,(SUM(Case when descrn = 1 then score end)-SUM(Case when rn = 1 then score end))*1.0/SUM(Case when rn = 1 then score end) as imp
From t
Group by id
/*
with t1 as(
Select id,MAX(dt) as maxdt,MIN(dt) as mindt
From #t
Group by id)
Select t1.id,(t2.score-t3.score)*1.0/ t3.score as imp
from t1
Inner Join #t as t2
On t1.id = t2.id
And t1.maxdt = t2.dt
Inner jOin #t as t3
On t1.id = t3.id
And t1.mindt = t3.dt
*/
-----------------------------------------
Select t1.id,(t2.score- t1.score)*1.00/ t1.score
From #t as t1
Inner JOin #t as t2
On t1.id = t2.id
Where Not Exists (
Select *
From #t as t3
Where t3.id = t1.id
And t3.dt < t1.dt)
And Not Exists(
Select *
From #t as t4
Where t2.id = t4.id
And t4.dt > t2.dt)
自己测试了一下，就目前的数据量，第三种方法效率最高，第二种效率最差。

Answer 7

顺便也测试了一下8楼9楼的方法，跟我第三种方法相比，效率分别是85%，15%，
可能是因为他用了Top关键字，排序占了很大的开销。