表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
……
……
希望能查询得到各个用户在连续最近的时间间隔里的数据差额。
Table1如:UserName Time1 Data1
201 20070524143950 12
202 20070524144950 11
……
……
201 20070624153950 18
202 20070624154950 25
……
……
希望得到结果如下
201 20070624153950 6
202 20070624154950 14
……
……
from table1 a
inner join table1 b
on a.UserName=b.UserName
and b.Time1=(seelct max(Time1) from table1 where UserName=a.UserName and Time1<a.Time1)
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.UserNameDROP TABLE #Table1,#T1
/*
201 20070624153950 6
202 20070624154950 14
*/
from
(select a.UserName,datediff(s,stuff(stuff(Stuff(a.Time1,9,0,' '),12,0,':'),15,0,':'),stuff(stuff(Stuff(b.Time1,9,0,' '),12,0,':'),15,0,':')) as Time1
,b.Data1=a.Data1 as Data1
from Table1 a inner join Table1 b on a.UserName=b.UserName
and a.Time1<b.Time1) c
inner join
(select a.UserName,min(datediff(s,stuff(stuff(Stuff(a.Time1,9,0,' '),12,0,':'),15,0,':'),stuff(stuff(Stuff(b.Time1,9,0,' '),12,0,':'),15,0,':'))) as Time1
from Table1 a inner join Table1 b on a.UserName=b.UserName
and a.Time1<b.Time1
Group by a.UserName) d
on c.UserName=d.UserName and c.Time1=d.Time1
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
*/
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 * from #Table1select UserName,max(Time1), max(Data1)-min(Data1) from #Table1 group by UserNamedrop table #Table1
/*
201 20070624153950 6
202 20070624154950 14
*/
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 --1楼的方法更方便
select a.UserName,a.Time1,a.Data1-b.Data1 as Data1
from #Table1 a
inner join #Table1 b
on a.UserName=b.UserName
and b.Time1=(select max(Time1) from #Table1 where UserName=a.UserName and Time1<a.Time1)--下面这条功能是,过滤只留最后的一个时间段
AND NOT EXISTS(SELECT 1 FROM #Table1 WHERE UserName=a.UserName and Time1>a.Time1) DROP TABLE #Table1
/*
201 20070624153950 6
202 20070624154950 14
*/