你的位置:首页 > 数据库

[数据库]分解跨天的时间数据


数据库环境:SQL SERVER 2005

  在百度知道看到这道题目,原问题链接 MS SQL数据库关于时间循环如何处理跨天问题

  有如下表:
  -----------------------------------------------------------------------------------------------------------------
  设备 开始时间 结束时间 总时间(分钟)
  A 2015-08-01 17:06:49 2015-08-02 03:41:32 635
  B 2015-08-01 20:54:42 2015-08-03 23:59:59 3605
  ----------------------------------------------------------------------------------------------------------------
  需求:
  1、将每个设备的时间段循环得出每天总时间
  2、1天的时间计算结果必须是1440
  3、不能用游标方式处理
  需求结果集表如下:
  -----------------------------------------------------------------------------------------------------------------
  设备 开始时间 结束时间 总时间(分钟)
  A 2015-08-01 17:06:49 2015-08-01 23:59:59 413
  A 2015-08-02 00:00:00 2015-08-02 03:41:32 222
  B 2015-08-01 20:54:42 2015-08-01 23:59:59 185
  B 2015-08-02 00:00:00 2015-08-02 23:59:59 1440
  B 2015-08-03 00:00:00 2015-08-03 23:59:59 1440
  ----------------------------------------------------------------------------------------------------------------------

  

  该问题已有网友用递归实现,如果不用递归,我们该怎么处理呢?下面,我说一下我的实现思路:

  1.求出每个设备开始时间和结束时间相差的天数

  2.生成分解后的新时间

  3.求出分解后每个设备每天的时间差(分钟),如果是一整天,则计为1440;

  如果是最后一天,则用总时间减去前面的时间总和。

  SQL实现

/*测试数据*/WITH  x0     AS ( SELECT  'A' AS sb ,            CONVERT(DATETIME, '2015-08-01 17:06:49') AS date_begin ,            CONVERT(DATETIME, '2015-08-02 03:41:32') AS date_end ,            635 AS total_time        UNION ALL        SELECT  'B' AS sb ,            CONVERT(DATETIME, '2015-08-01 20:54:42') AS date_begin ,            CONVERT(DATETIME, '2015-08-03 23:50:52') AS date_end ,            3056 AS total_time        UNION ALL        SELECT  'C' AS sb ,            CONVERT(DATETIME, '2015-08-04 12:40:20') AS date_begin ,            CONVERT(DATETIME, '2015-08-05 23:59:59') AS date_end ,            2119 AS total_time       ),/*计算两个时间点之间相差的天数*/    x1     AS ( SELECT  sb ,            date_begin ,            date_end ,            total_time ,            DATEDIFF(day, date_begin, date_end) AS cacl_day--开始时间和结束时间相差的天数        FROM   x0       ),/*将隔天的时间分解*/    x2     AS ( SELECT  sb ,            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'            END AS date_end ,            total_time ,            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  sb ,            date_begin ,            date_end ,            total_time ,            CASE WHEN CONVERT(CHAR(8), date_begin, 108) = '00:00:00'                 AND CONVERT(CHAR(8), date_end, 108) = '23:59:59'               THEN 1440               ELSE DATEDIFF(MINUTE, date_begin, date_end)            END AS cacl_time ,--如果是一整天,那么是1440分钟,否则,2个时间相减            flag ,            ROW_NUMBER() OVER ( PARTITION BY sb ORDER BY date_end ) AS rn--行号,用于后面统计各天的剩余分钟        FROM   x2       )  SELECT sb ,      date_begin ,      date_end ,      CASE WHEN flag = 1 THEN total_time - ( SELECT  SUM(cacl_time)                          FROM   x3 x                          WHERE  x.sb = x3.sb                              AND x.rn < x3.rn                         )         ELSE cacl_time--最后一天的耗时是总时间减去前面的时间总和      END AS cacl_time  FROM  x3 

View Code

  我自己添加了一些测试数据,先看下原表的数据

  分解后的时间

  感兴趣的朋友,可以对比一下这2种方法实现的异同。