数据表记录了用户每次离开房间时的离开时间、游戏时长、在线时长，现在需统计用户每天的在线时长数据。现实中存在用户前一天进入了房间玩游戏，然后在次日离开房间，对于这类数据在数据库中依然只会记录一条记录，对应其离开房间时间、游戏时长、在线时长。如果我们直接用此记录查询，会出现用户当天在线时长>24小时的现象，业务上我们可以解释为跨天在线(游戏)，但在逻辑上当天在线时长>24小时，显然是有问题的。因此我们需要将跨天的记录进行拆分。
用图形描述我们期望达到的效果：
如果起止时间跨天的，我们以0点将其拆分成多条记录，计算各区间的在线时长，然后将游戏时长从起始时间开始填充，游戏时长用完补0。
参考，得到满足需求的处理脚本：
WITH x0 AS ( SELECT 'A' AS Ident ,139185 AS PlayTime ,DATEADD(SS,-256199,'2016-01-11 20:59:43.243') date_begin ,CONVERT(DATETIME ,'2016-01-11 20:59:43.243') AS date_end ,256199 AS OnlineTime UNION ALL SELECT 'B' AS Ident ,6085 AS PlayTime ,DATEADD(SS,-6199,'2015-10-18 00:12:42.840') date_begin ,CONVERT(DATETIME ,'2015-10-18 00:12:42.840') AS date_end ,6199 AS OnlineTime ),/*计算两个时间点之间相差的天数*/ x1 AS ( SELECT Ident ,PlayTime ,date_begin ,date_end ,OnlineTime ,DATEDIFF(DAY ,date_begin ,date_end) AS cacl_day--开始时间和结束时间相差的天数 FROM x0 ),/*将隔天的时间分解*/ x2 AS ( SELECT Ident ,PlayTime ,CASE WHEN msv.number=0 THEN date_begin ELSE CONVERT(VARCHAR(10) ,DATEADD(DAY ,msv.number ,date_begin) ,120) END AS date_begin ,CASE WHEN msv.number=x.cacl_day THEN date_end ELSE CONVERT(VARCHAR(10) ,DATEADD(DAY ,msv.number ,date_begin) ,120) +' 23:59:59.997' END AS date_end ,OnlineTime ,CASE WHEN msv.number=x.cacl_day THEN 1 ELSE 0 END AS flag--如果是最后一天，标识为1，否则为0 FROM x1 x ,MASTER..spt_values msv WHERE msv.type = 'P' AND msv.number<= x.cacl_day ), x3 AS ( SELECT Ident ,PlayTime ,date_begin ,date_end ,OnlineTime ,CASE WHEN CONVERT(CHAR(8) ,date_end ,108)='23:59:59' THEN DATEDIFF(SS ,date_begin ,date_end)+1 ELSE DATEDIFF(SS ,date_begin ,date_end) END AS cacl_OnlineTime ,flag ,ROW_NUMBER() OVER(PARTITION BY Ident ORDER BY date_end) AS rn--行号 FROM x2 ) SELECT * ,CASE WHEN playtime>=( SELECT SUM(cacl_OnlineTime) FROM x3 x WHERE x.Ident = x3.Ident AND x.rn<= x3.rn ) THEN cacl_OnlineTime WHEN playtime<( SELECT SUM(cacl_OnlineTime) FROM x3 x WHERE x.Ident = x3.Ident AND x.rn<= x3.rn ) AND playtime>( SELECT ISNULL(SUM(cacl_OnlineTime) ,0) FROM x3 x WHERE x.Ident = x3.Ident AND x.rn<x3.rn ) THEN playtime-( SELECT ISNULL(SUM(cacl_OnlineTime) ,0) FROM x3 x WHERE x.Ident = x3.Ident AND x.rn<x3.rn ) ELSE 0 END cacl_PlayTime FROM x3 ORDER BY Ident,date_begin
数据是能拆分，但如果记录数较多，最好分批处理。如果还要与原数据表中非跨天的记录结合查询，即用拆分好的记录替代原跨天记录，这个消耗也不小。
最近遇到拆分跨天问题，表中跨天数据情况如下：
可以看出，跨1、2天的占了99%，如果用上面的语句每次跑一个用户，测试结果是每1000用户(约5000条记录)拆分需11分钟，对于10W用户需要将近一天时间！
和老大说明处理过程，可能需要很长时间。老大指出可以换种处理方式，以所跨天数为维度，跨一天的拆成开始时间-当天23:59:59，次日0点-结束时间；跨两天拆成开始时间-当天23:59:59，次日0点-次日23:59:59，第三天0点-结束时间...这样就把问题划分成几个大集合，而不是以用户维度的小集合。再把游戏时间从开始时间填充即可。
总思路：将原始数据通过导入导出向导导入到测试库，将跨天的数据insert到测试库，处理跨天数据，从测试库下的原始数据表删除跨天纪录，将拆分好的数据insert到测试库的原始数据表。
待拆分的记录
拆分后的记录
附上代码
--使用导入导出向导 导出原始数据到测试库 SELECT * FROM [SourceDB].[dbo].[RecordUserLeave] (nolock) WHERE LeaveTime<'20160201' --跨天数据INSERT到测试库 SELECT [UserID] ,[Score] ,[Revenue] ,[KindID] ,[ServerID] ,case when [PlayTimeCount]>[OnLineTimeCount] then [OnLineTimeCount] else [PlayTimeCount] end [PlayTimeCount] ,[OnLineTimeCount] ,DATEADD(SS,-OnLineTimeCount,LeaveTime) EnterTime ,[LeaveTime] ,[deviceType] into TargetDB.dbo.RecordUserLeave_Kua FROM [SourceDB].[dbo].[RecordUserLeave] (nolock) where LeaveTime<'20160201' and DATEDIFF(DAY,DATEADD(SS,-OnLineTimeCount,LeaveTime),[LeaveTime])>0 /********跨天数据按天拆分********/ --创建同结构的数据表，保存拆分后的记录 SELECT * INTO RecordUserLeave_KuaFinish FROM TargetDB.dbo.RecordUserLeave_Kua WHERE 1=0 --跨一天(DATEDIFF(day,[EnterTime],[LeaveTime])=1) --day1 INSERT INTO RecordUserLeave_KuaFinish ( UserID ,Score ,Revenue ,KindID ,ServerID ,PlayTimeCount ,OnLineTimeCount ,EnterTime ,LeaveTime ,deviceType ) SELECT UserID ,Score ,Revenue ,KindID ,ServerID --,PlayTimeCount,OnLineTimeCount,EnterTime,LeaveTime ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN PlayTimeCount ELSE DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) END PlayTimeCount ,DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) OnLineTimeCount ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 1 UNION ALL --day2 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN 0 ELSE PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) END ,DATEDIFF(SS ,CONVERT(VARCHAR(10) ,LeaveTime ,120) ,LeaveTime) OnLineTimeCount ,CONVERT(VARCHAR(10) ,LeaveTime ,120)+' 00:00:00.000' EnterTime ,LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 1 --跨两天(DATEDIFF(day,[EnterTime],[LeaveTime])=2) INSERT INTO RecordUserLeave_KuaFinish ( UserID ,Score ,Revenue ,KindID ,ServerID ,PlayTimeCount ,OnLineTimeCount ,EnterTime ,LeaveTime ,deviceType ) --day1 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN PlayTimeCount ELSE DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) END PlayTimeCount ,DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) OnLineTimeCount ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 2 UNION ALL --day2 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =1 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 2 UNION ALL --day3 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=1 THEN 0 ELSE PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)) END PlayTimeCount ,DATEDIFF(SS ,CONVERT(VARCHAR(10) ,LeaveTime ,120) ,LeaveTime) OnLineTimeCount ,CONVERT(VARCHAR(10) ,LeaveTime ,120)+' 00:00:00.000' EnterTime ,LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 2 --跨三天(DATEDIFF(day,[EnterTime],[LeaveTime])=3) INSERT INTO RecordUserLeave_KuaFinish ( UserID ,Score ,Revenue ,KindID ,ServerID ,PlayTimeCount ,OnLineTimeCount ,EnterTime ,LeaveTime ,deviceType ) --day1 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN PlayTimeCount ELSE DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) END PlayTimeCount ,DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) OnLineTimeCount ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 3 UNION ALL --day2 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =1 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 3 UNION ALL --day3 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=1 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =2 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 3 UNION ALL --day4 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=2 THEN 0 ELSE PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+3 ,120)) END PlayTimeCount ,DATEDIFF(SS ,CONVERT(VARCHAR(10) ,LeaveTime ,120) ,LeaveTime) OnLineTimeCount ,CONVERT(VARCHAR(10) ,LeaveTime ,120)+' 00:00:00.000' EnterTime ,LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 3 --跨四天(DATEDIFF(day,[EnterTime],[LeaveTime])=4) INSERT INTO RecordUserLeave_KuaFinish ( UserID ,Score ,Revenue ,KindID ,ServerID ,PlayTimeCount ,OnLineTimeCount ,EnterTime ,LeaveTime ,deviceType ) --day1 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN PlayTimeCount ELSE DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) END PlayTimeCount ,DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) OnLineTimeCount ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 4 UNION ALL --day2 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =1 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 4 UNION ALL --day3 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=1 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =2 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 4 UNION ALL --day4 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=2 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =3 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+3 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+3 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+3 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 4 UNION ALL --day5 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=3 THEN 0 ELSE PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+4 ,120)) END PlayTimeCount ,DATEDIFF(SS ,CONVERT(VARCHAR(10) ,LeaveTime ,120) ,LeaveTime) OnLineTimeCount ,CONVERT(VARCHAR(10) ,LeaveTime ,120)+' 00:00:00.000' EnterTime ,LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 4 --跨五天(DATEDIFF(day,[EnterTime],[LeaveTime])=5) INSERT INTO RecordUserLeave_KuaFinish ( UserID ,Score ,Revenue ,KindID ,ServerID ,PlayTimeCount ,OnLineTimeCount ,EnterTime ,LeaveTime ,deviceType ) --day1 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN PlayTimeCount ELSE DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) END PlayTimeCount ,DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) OnLineTimeCount ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 5 UNION ALL --day2 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =0 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =1 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+1 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 5 UNION ALL --day3 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=1 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =2 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+2 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 5 UNION ALL --day4 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=2 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =3 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+3 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+3 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+3 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 5 UNION ALL --day5 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=3 THEN 0 WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) =4 THEN PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+4 ,120)) ELSE 86400 END PlayTimeCount ,86400 OnLineTimeCount ,CONVERT(VARCHAR(10) ,EnterTime+4 ,120)+' 00:00:00.000' EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+4 ,120)+' 23:59:59.997' LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 5 UNION ALL --day6 SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,CASE WHEN DATEDIFF(DAY ,EnterTime ,DATEADD(ss ,PlayTimeCount ,EnterTime)) <=4 THEN 0 ELSE PlayTimeCount-DATEDIFF(SS ,EnterTime ,CONVERT(VARCHAR(10) ,EnterTime+5 ,120)) END PlayTimeCount ,DATEDIFF(SS ,CONVERT(VARCHAR(10) ,LeaveTime ,120) ,LeaveTime) OnLineTimeCount ,CONVERT(VARCHAR(10) ,LeaveTime ,120)+' 00:00:00.000' EnterTime ,LeaveTime ,deviceType FROM [TargetDB].[dbo].[RecordUserLeave_Kua] (nolock) WHERE DATEDIFF(DAY ,[EnterTime] ,[LeaveTime]) = 5 --五天以上的用下面语句直接获取 ;WITH x1 AS( SELECT top 50 * --注意top N取值需>=满足where条件的记录数 ,DATEDIFF(DAY ,EnterTime ,LeaveTime) cacl_day FROM TargetDB.dbo.RecordUserLeave_Kua (nolock) WHERE DATEDIFF(day,[EnterTime],[LeaveTime])>5 ) , x2 AS( SELECT x.UserID ,x.Score ,x.Revenue ,x.KindID ,x.ServerID ,x.PlayTimeCount --,x.OnLineTimeCount ,EnterTime EnterTime_old ,LeaveTime LeaveTime_old ,CASE WHEN msv.number=0 THEN EnterTime ELSE CONVERT(VARCHAR(10) ,DATEADD(DAY ,msv.number ,EnterTime) ,120) END AS EnterTime ,CASE WHEN msv.number=x.cacl_day THEN LeaveTime ELSE CONVERT(VARCHAR(10) ,DATEADD(DAY ,msv.number ,EnterTime) ,120) +' 23:59:59.997' END AS LeaveTime ,x.deviceType ,CASE WHEN msv.number=x.cacl_day THEN 1 ELSE 0 END AS flag--如果是最后一天，标识为1，否则为0 FROM x1 x ,MASTER..spt_values msv WHERE msv.type = 'P' AND msv.number<= x.cacl_day ) , x3 AS( SELECT * ,CASE WHEN CONVERT(CHAR(8) ,LeaveTime ,108)='23:59:59' THEN DATEDIFF(SS ,EnterTime ,LeaveTime) +1 ELSE DATEDIFF(SS ,EnterTime ,LeaveTime) END cacl_OnlineTime ,ROW_NUMBER() OVER(PARTITION BY UserID ,EnterTime_Old ORDER BY LeaveTime) AS rn--行号 FROM x2 ) INSERT INTO RecordUserLeave_KuaFinish(UserID,Score,Revenue,KindID,ServerID,EnterTime,LeaveTime,deviceType,OnLineTimeCount,PlayTimeCount) SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,EnterTime ,LeaveTime ,deviceType --,flag ,cacl_OnlineTime --,rn ,CASE WHEN PlayTimeCount>=( SELECT SUM(cacl_OnlineTime) FROM x3 x WHERE x.UserID = x3.UserID AND x.EnterTime_old = x3.EnterTime_old AND x.rn<= x3.rn ) THEN cacl_OnlineTime WHEN PlayTimeCount<( SELECT SUM(cacl_OnlineTime) FROM x3 x WHERE x.UserID = x3.UserID AND x.EnterTime_old = x3.EnterTime_old AND x.rn<= x3.rn ) AND PlayTimeCount>( SELECT ISNULL(SUM(cacl_OnlineTime) ,0) FROM x3 x WHERE x.UserID = x3.UserID AND x.EnterTime_old = x3.EnterTime_old AND x.rn<x3.rn ) THEN PlayTimeCount-( SELECT ISNULL(SUM(cacl_OnlineTime) ,0) FROM x3 x WHERE x.UserID = x3.UserID AND x.EnterTime_old = x3.EnterTime_old AND x.rn<x3.rn ) ELSE 0 END cacl_PlayTime FROM x3 ORDER BY UserID ,EnterTime --测试库的原数据表中删除跨天的数据 DELETE FROM TargetDB.[dbo].[RecordUserLeave] WHERE DATEDIFF(DAY ,DATEADD(SS ,-OnLineTimeCount ,LeaveTime) ,[LeaveTime])>0 --将拆分好的数据插入测试库原数据表 INSERT INTO TargetDB.[dbo].[RecordUserLeave] ( UserID ,Score ,Revenue ,KindID ,ServerID ,PlayTimeCount ,OnLineTimeCount ,LeaveTime ,deviceType ) SELECT UserID ,Score ,Revenue ,KindID ,ServerID ,PlayTimeCount ,OnLineTimeCount ,LeaveTime ,deviceType FROM TargetDB.[dbo].RecordUserLeave_KuaFinish
用后面这种方式，处理拆分跨天数据只需13s