求一个统计的 SQL 语句 ，可能不是很复杂，但是有点麻烦。

Question

CREATE TABLE #UserLogin
(
LoginTime DATETIME DEFAULT GETDATE(),
Account_ID VARCHAR(16),
LoginState INT
)
GOINSERT 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
GOINSERT 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
=========================================================================
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.999
第二步：UpTime DownTime                   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
.
.
.
*/
SQLselect统计

Answer 1

因为 '上线时间' 与 '下线时间'一定是成对出现的，否则一定是数据有问题所以 SumAccount=SUM（下线时间）-SUM（上线时间）

Answer 2

没有   那个 test  的数据就是一个说明，有可能从昨天一直在线到今天才下线。

Answer 3

SELECT '2013-05-15 08:01:03','test',0 UNION ALL            --表示从昨天到今天一直在是的，但是是从昨天一直在线到现在，到 08:01:03  才下线，然后就有一条下线的（State 为0 ）的记录

Answer 4

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)
第一个应该是这样吧...

Answer 5

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',0INSERT 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)
*/

Answer 6

CREATE TABLE #UserLogin
(
LoginTime DATETIME DEFAULT GETDATE(),
Account_ID VARCHAR(16),
LoginState INT
)
GOINSERT 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
GOINSERT 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

Answer 7

CREATE TABLE #UserLogin
(
LoginTime DATETIME DEFAULT GETDATE(),
Account_ID VARCHAR(16),
LoginState INT
)
GOINSERT 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
GOINSERT INTO #UserLogin
SELECT '2013-05-15 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,(case when b.LoginTime<a.logintime then DATEADD(day,1,b.logintime) else b.logintime end))
 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
/*
(2 行受影响)
account_id       loginstate  uptime                  downtime                sumaccount
---------------- ----------- ----------------------- ----------------------- -----------
test             1           2013-05-15 19:30:03.000 2013-05-15 08:01:03.000 13
zy_luopeng       1           2013-05-15 08:01:03.000 2013-05-15 10:30:03.000 2
zy_luopeng       1           2013-05-15 14:17:03.000 2013-05-15 19:50:03.000 5(3 行受影响)*/

Answer 8

你这个是错误的.... test 的数据应该是   2013-05-15 00:00:00.000 - 2013-05-15 08:01:03

Answer 9

、
谢谢大家，我已经写出来了用游标写的CREATE TABLE #UserLoginState(Account varchar(16),UpTime DATETIME DEFAULT CONVERT(VARCHAR(19),GETDATE(),23)+' 00:00:00.000',DownTime DATETIME DEFAULT CONVERT(VARCHAR(19),GETDATE(),23)+' 23:59:59.999')
GO
DECLARE Account CURSOR FOR SELECT Account_ID FROM #UserLogin GROUP BY Account_ID
DECLARE @Account VARCHAR(12)
OPEN Account
fetch next from Account into @Account
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE LP CURSOR FOR SELECT * FROM #UserLogin WHERE Account_ID = @Account ORDER BY Account_ID ASC ,LoginTime ASC
DECLARE @LoginTime DATETIME,@Account_ID VARCHAR(16),@LoginState INT
DECLARE @upTime VARCHAR(19)
DECLARE @downTime VARCHAR(19)
DECLARE @isFirstLogin INT
SET @isFirstLogin = 1
OPEN LP
fetch next from LP into @LoginTime,@Account_ID,@LoginState
while(@@fetch_status=0)BEGIN
--SELECT  @LoginTime,@Account_ID,@LoginState

IF @LoginState =1
BEGIN
SET @upTime = convert(varchar(19),@LoginTime,20)
END
IF @LoginState = 0
BEGIN
SET @downTime = convert(varchar(19),@LoginTime,20)
IF @isFirstLogin = 1
BEGIN
--表示第一次就是下线状态
SET @upTime = CONVERT(VARCHAR(10),GETDATE(),23)+' 00:00:00.000'
END
END
IF @upTime <> '' AND @downTime <> ''
BEGIN
INSERT INTO #UserLoginState VALUES (@Account_ID,@upTime,@downTime)
SET @upTime = ''
SET @downTime = ''
END
SET @isFirstLogin = 0
--继续提下一行
fetch next from LP into @LoginTime,@Account_ID,@LoginState
END
IF @upTime <> '' AND @downTime = '' --表示今天有上线时间没有下线时间 那么表示今天是通宵
BEGIN
set @downTime = CONVERT(VARCHAR(10),GETDATE(),23)+' 23:59:59.999'
INSERT INTO #UserLoginState VALUES (@Account_ID,@upTime,@downTime)
SET @upTime = ''
SET @downTime = ''
END --关闭游标
close LP
--删除游标
deallocate LP
fetch next from Account into @Account
END
CLOSE Account
deallocate Account
GO--结果一
SELECT * FROM #UserLoginState uls--结果二
--目前只想到了这种方法
DECLARE @startTime DATETIME,@endTime DATETIME
SET @startTime = CONVERT(VARCHAR(19),GETDATE(),23)+' 00:00:00.000'
SET @endTime =  DATEADD(HH,1,@startTime)
WHILE @endTime <= CONVERT(VARCHAR(19),GETDATE(),23)+' 23:59:59.999'
BEGIN

SELECT CONVERT(VARCHAR(19),@startTime,20)+'------'+CONVERT(VARCHAR(19),@endTime,20) AS '时间段',ISNULL( COUNT(1),0) AS '在线人数合计' FROM #UserLoginState WHERE UpTime <= @startTime AND DownTime>= @endTime  GROUP BY Account
SET @startTime = DATEADD(HH,1,@endTime)
SET @endTime =  DATEADD(HH,1,@startTime)
ENDsc273607742 和 AP 大神的方法我等下要研究下,先去写别的了,结贴了再说!~tangguangqiang 额,可能我表述的不清楚,不好意思!~

Answer 10

你这个是错误的.... test 的数据应该是   2013-05-15 00:00:00.000 - 2013-05-15 08:01:03
糊涂了，
那看看7楼的吧。