求个查询语句。解决马上给分。在先等。谢谢。
表Table1里有用户名列UserName(string),有时间列Time1(string),数据形式如:20070524143950,另有数据列Data1(double).
希望能查询得到各个用户在连续最近的时间间隔里的数据差额。


Table1如：

UserName                 Time1                                 Data1
201                           20070524143950               12
202                           20070524144950               11
……
……
201                           20070624153950               18
202                           20070624154950               25
……
……


希望得到结果如下
201                           20070624153950               6
202                           20070624154950               14  
……
……




------解决方案--------------------
try:
CREATE TABLE #Table1(UserName nvarchar(20),Time1 nvarchar(40),Data1 int)
INSERT INTO #Table1
SELECT '201 ', '20070524143950 ',12 UNION ALL
SELECT '202 ', '20070524144950 ',11 UNION ALL
SELECT '201 ', '20070624153950 ',18 UNION ALL
SELECT '202 ', '20070624154950 ',25
--取最大值
SELECT * INTO #T1 FROM #Table1 AS A WHERE NOT EXISTS(SELECT 1 FROM #Table1 AS B WHERE B.UserName=A.UserName AND B.Time1> A.Time1 )
--计算结果
SELECT A.UserName,C.Time1,C.Data1-B.Data1 AS Data1
FROM
(SELECT UserName,MAX(Time1) AS Time1 FROM #Table1 AS A1
WHERE NOT EXISTS(SELECT 1 FROM #T1 AS B1 WHERE B1.UserName=A1.UserName AND A1.Time1=B1.Time1)
GROUP BY A1.UserName)AS A
INNER JOIN #Table1 AS B ON B.UserName=A.UserName AND B.Time1=A.Time1
INNER JOIN #T1 AS C ON C.UserName=A.UserName

DROP TABLE #Table1,#T1
/*
201 20070624153950 6
202 20070624154950 14
*/

------解决方案--------------------
CREATE TABLE #Table1(UserName nvarchar(20),Time1 nvarchar(40),Data1 int)
INSERT INTO #Table1
SELECT '201 ', '20070524133950 ',12 UNION ALL
SELECT '201 ', '20070524143950 ',12 UNION ALL
SELECT '202 ', '20070524144950 ',11 UNION ALL
SELECT '201 ', '20070624153950 ',18 UNION ALL
SELECT '202 ', '20070624154950 ',25


select UserName,max(Time1),-sum(Data1) from
(
select UserName,Time1 ,Data1*(-2) Data1 from #Table1 A where not exists (select 1 from #Table1 where a.UserName=UserName and a.Time1 <Time1)
union
select UserName,Time1 ,Data1 from #Table1 a where (select count(1) from #Table1 where a.UserName=UserName and a.Time1 <Time1) <2
) a
group by UserName

/*
201 20070624153950 6
202 20070624154950 14
*/