求一个统计的 SQL 语句 ，可能不是很复杂，但是有点麻烦。
CREATE TABLE #UserLogin
(
LoginTime DATETIME DEFAULT GETDATE(),
Account_ID VARCHAR(16),
LoginState INT
)
GO
INSERT INTO #UserLogin
SELECT '2013-05-15 08:01:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 10:30:03','zy_luopeng',0 UNION ALL
SELECT '2013-05-15 14:17:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 19:50:03','zy_luopeng',0
GO
INSERT INTO #UserLogin
SELECT '2013-05-15 08:01:03','test',0 UNION ALL--表示从昨天到今天一直在
SELECT '2013-05-15 19:30:03','test',1--表示一直玩到今天通宵
GO
SELECT * FROM #UserLogin
/*
想要获取的结果
第一步： 到这步实在想了半天都不知道要怎么得到这种结果。
这样写又不对。。
SELECT Account_ID,
CASE WHEN LoginState = 1 THEN
LoginTime ELSE 0 END AS '上线时间' ,
CASE WHEN LoginState = 0 THEN
LoginTime ELSE 0 END AS '下线时间'
FROM #UserLogin
=========================================================================
AccountUpTimeDownTime
=========================================================================
zy_luopeng2013-05-15 08:01:03.0002013-05-15 10:30:03.000
zy_luopeng2013-05-15 14:17:03.0002013-05-15 19:50:03.000
test2013-05-15 00:00:00.0002013-05-15 08:01:03.000
test2013-05-15 19:30:03.0002013-05-15 23:59:59.999
第二步：
UpTimeDownTime SumAccount
====================================================================================
2013-05-15 08:00:00 2013-05-15 09:00:00 1
2013-05-15 09:00:00 2013-05-15 10:00:00 1
.
.
.
2013-05-15 19:00:00 2013-05-15 20:00:00 2
.
.
.
*/
所以 SumAccount=SUM（下线时间）-SUM（上线时间）
[解决办法]
with tb(LoginTime,Account_ID,LoginState)as(
SELECT '2013-05-15 08:01:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 10:30:03','zy_luopeng',0 UNION ALL
SELECT '2013-05-15 14:17:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 19:50:03','zy_luopeng',0 UNION ALL
SELECT '2013-05-15 08:01:03','test',0 UNION ALL
SELECT '2013-05-15 19:30:03','test',1
)
--统计当天正常上下线的
select Account_ID Account,LoginTime UpTime ,
(select top 1 logintime from tb where a.Account_ID=Account_ID and LoginTime>a.LoginTime and LoginState=0
and DATEDIFF(day,a.logintime,logintime)<1)downtime from tb a
where LoginState=1 and (select top 1 logintime from tb where a.Account_ID=Account_ID and LoginTime>a.LoginTime and LoginState=0
and DATEDIFF(day,a.logintime,logintime)<1) is not null
union all
--统计早上0点上的
select Account_ID,DATEADD(day,0,datediff(DAY,0,LoginTime)),LoginTime from tb a
where LoginState=0 and
not exists(select 1 from tb where a.account_id=account_id and DATEDIFF(day,logintime,a.logintime)<1 and LoginState=1
and LoginTime<a.LoginTime)
union all
--统计晚上没下线的
select Account_ID,LoginTime,dateadd(SECOND,-1,DATEADD(day,1,datediff(DAY,0,LoginTime))) from tb a
where LoginState=1 and
not exists(select 1 from tb where a.account_id=account_id and DATEDIFF(day,a.logintime,logintime)<1 and LoginState=0
and LoginTime>a.LoginTime)
CREATE TABLE #UserLogin
( LoginTime DATETIME DEFAULT GETDATE(),
Account_ID VARCHAR(16),
LoginState INT
)
INSERT INTO #UserLogin
SELECT '2013-05-15 08:01:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 10:30:03','zy_luopeng',0 UNION ALL
SELECT '2013-05-15 14:17:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 19:50:03','zy_luopeng',0
INSERT INTO #UserLogin
SELECT '2013-05-15 08:01:03','test',0 UNION ALL --表示从昨天到今天一直在
SELECT '2013-05-15 19:30:03','test',1 --表示一直玩到今天通宵
with c as
(select LoginTime,Account_ID,LoginState
from #UserLogin a
where
(a.LoginState=0 and not exists
(select 1 from #UserLogin b where b.Account_ID=a.Account_ID and b.LoginTime<a.LoginTime and b.LoginState=1))
or
(a.LoginState=1 and not exists
(select 1 from #UserLogin b where b.Account_ID=a.Account_ID and b.LoginTime>a.LoginTime and b.LoginState=0))
),
e as
(select LoginTime,Account_ID,LoginState from #UserLogin
except
select LoginTime,Account_ID,LoginState from c
union all
select case when c.LoginState=d.LoginState
then c.LoginTime
when c.LoginState=0 and c.LoginState<>d.LoginState
then cast(convert(varchar,c.LoginTime,111)+' 00:00:00' as datetime)
when c.LoginState=1 and c.LoginState<>d.LoginState
then cast(convert(varchar,c.LoginTime,111)+' 23:59:59' as datetime)
end 'LoginTime',
Account_ID,
d.LoginState
from c
cross join
(select 1 'LoginState' union all select 0 'LoginState') d
)
select Account_ID 'Account',[1] 'UpTime',[0] 'DownTime'
from (select LoginTime,Account_ID,LoginState,
(row_number() over(order by getdate())+1)/2 'rn' from e) f
pivot(max(LoginTime) for LoginState in([1],[0])) p
/*
Account UpTime DownTime
---------------- ----------------------- -----------------------
zy_luopeng 2013-05-15 08:01:03.000 2013-05-15 10:30:03.000
zy_luopeng 2013-05-15 14:17:03.000 2013-05-15 19:50:03.000
test 2013-05-15 00:00:00.000 2013-05-15 08:01:03.000
test 2013-05-15 19:30:03.000 2013-05-15 23:59:59.000
(4 row(s) affected)
*/
CREATE TABLE #UserLogin
(
LoginTime DATETIME DEFAULT GETDATE(),
Account_ID VARCHAR(16),
LoginState INT
)
GO
INSERT INTO #UserLogin
SELECT '2013-05-15 08:01:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 10:30:03','zy_luopeng',0 UNION ALL
SELECT '2013-05-15 14:17:03','zy_luopeng',1 UNION ALL
SELECT '2013-05-15 19:50:03','zy_luopeng',0
GO
INSERT INTO #UserLogin
SELECT '2013-05-16 08:01:03','test',0 UNION ALL--表示从昨天到今天一直在
SELECT '2013-05-15 19:30:03','test',1--表示一直玩到今天通宵
GO
select a.account_id,a.loginstate,uptime=a.logintime,downtime=b.logintime,sumaccount=datediff(hour,a.LoginTime,b.LoginTime) from (
select *,ROW=ROW_NUMBER()over(partition by account_id order by Account_ID,LoginTime) from #UserLogin where LoginState=1 )a join (
select *,ROW=ROW_NUMBER()over(partition by account_id order by Account_ID,LoginTime) from #UserLogin where LoginState=0 ) b
on a.Account_ID=b.Account_ID and a.ROW=b.ROW
