拆分跨天的时间数据

数据表记录了用户每次离开房间时的离开时间、游戏时长、在线时长，现在需统计用户每天的在线时长数据。现实中存在用户前一天进入了房间玩游戏，然后在次日离开房间，对于这类数据在数据库中依然只会记录一条记录，对应其离开房间时间、游戏时长、在线时长。如果我们直接用此记录查询，会出现用户当天在线时长>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