sql查询中对数据处理问题
字段
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
有比较简便的方式没有，我自己想的都特别麻烦
[解决办法]
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
[解决办法]
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)
