求个查询语句。解决马上给分。在先等。谢谢。
表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
*/
