小菜来袭 2016-12-29 03:40 采纳率: 50%
浏览 856

mss sql语句脚本检测数据的问题 请大神指点指点

表名为data表 里面的字段为id,v1,v2,v3,time 有3条这样的数据 id分别为1,2,3
脚本在每天的23:59:59启动 检测当天24:00:00以后的数据 3*24=72 如果大于等于72 不做操作
小于72的 检查每小时内是否 id为1，2，3的数据 如果少了一条 比如只有1,3 那么取最近一条id
d2的数据插进去 时间修改当前时间

有点复杂 没什么思路呀 请大神指教

  • 写回答

2条回答 默认 最新

  • Tiger_Zhao 2016-12-29 06:52
    关注
    /* 测试数据。当天3记录作为模板，第二天只有3记录
DROP TABLE data

;WITH t(id,v1,v2,v3,time) AS (
    SELECT 1,10,20,30,DATEADD(day,-1,GETDATE()) UNION ALL
    SELECT 2,10,20,30,DATEADD(day,-1,GETDATE()) UNION ALL
    SELECT 3,10,20,30,DATEADD(day,-1,GETDATE()) UNION ALL
    SELECT 1,11,21,31,GETDATE() UNION ALL
    SELECT 2,11,21,31,GETDATE() UNION ALL
    SELECT 3,11,21,31,GETDATE()
)
SELECT *
  INTO data
  FROM t
*/

DECLARE @day datetime
-- 求明天的日期
-- 如果启动有延时，已经到了第二天，加1秒还是第二天
SET @day = CONVERT(datetime,
                   CONVERT(varchar(10),
                           DATEADD(second,1,GETDATE()),
                           120),
                   120)
-- 补全记录
;WITH a(id) AS (
    SELECT number
      FROM master..spt_values
     WHERE type = 'p'
       AND number BETWEEN 1 AND 3
),
b(dt1,dt2) AS (
    SELECT DATEADD(hour, number, @day),
           DATEADD(hour, number+1, @day)
      FROM master..spt_values
     WHERE type = 'p'
       AND number < 24
),
c(id,dt1,dt2) AS (
        SELECT a.id, b.dt1, b.dt2
          FROM a, b
)
INSERT INTO data
SELECT c.id,
       t.v1, t.v2, t.v3,
       c.dt1
  FROM c
   OUTER APPLY (
                    SELECT TOP 1 v1,v2,v3
                      FROM data
                     WHERE id = c.id
                       AND time < @day
                  ORDER BY time DESC
               ) t
 WHERE NOT EXISTS (SELECT *
                     FROM data
                    WHERE id = c.id
                      AND time >= c.dt1
                      AND time < c.dt2)

-- 结果
SELECT *
  FROM data
 ORDER BY time, id

             id          v1          v2          v3 time
----------- ----------- ----------- ----------- -----------------------
          1          10          20          30 2016-12-28 14:44:09.330
          2          10          20          30 2016-12-28 14:44:09.330
          3          10          20          30 2016-12-28 14:44:09.330
          1          10          20          30 2016-12-29 00:00:00.000
          2          10          20          30 2016-12-29 00:00:00.000
          3          10          20          30 2016-12-29 00:00:00.000
          1          10          20          30 2016-12-29 01:00:00.000
          2          10          20          30 2016-12-29 01:00:00.000
          3          10          20          30 2016-12-29 01:00:00.000
          1          10          20          30 2016-12-29 02:00:00.000
          2          10          20          30 2016-12-29 02:00:00.000
          3          10          20          30 2016-12-29 02:00:00.000
          1          10          20          30 2016-12-29 03:00:00.000
          2          10          20          30 2016-12-29 03:00:00.000
          3          10          20          30 2016-12-29 03:00:00.000
          1          10          20          30 2016-12-29 04:00:00.000
          2          10          20          30 2016-12-29 04:00:00.000
          3          10          20          30 2016-12-29 04:00:00.000
          1          10          20          30 2016-12-29 05:00:00.000
          2          10          20          30 2016-12-29 05:00:00.000
          3          10          20          30 2016-12-29 05:00:00.000
          1          10          20          30 2016-12-29 06:00:00.000
          2          10          20          30 2016-12-29 06:00:00.000
          3          10          20          30 2016-12-29 06:00:00.000
          1          10          20          30 2016-12-29 07:00:00.000
          2          10          20          30 2016-12-29 07:00:00.000
          3          10          20          30 2016-12-29 07:00:00.000
          1          10          20          30 2016-12-29 08:00:00.000
          2          10          20          30 2016-12-29 08:00:00.000
          3          10          20          30 2016-12-29 08:00:00.000
          1          10          20          30 2016-12-29 09:00:00.000
          2          10          20          30 2016-12-29 09:00:00.000
          3          10          20          30 2016-12-29 09:00:00.000
          1          10          20          30 2016-12-29 10:00:00.000
          2          10          20          30 2016-12-29 10:00:00.000
          3          10          20          30 2016-12-29 10:00:00.000
          1          10          20          30 2016-12-29 11:00:00.000
          2          10          20          30 2016-12-29 11:00:00.000
          3          10          20          30 2016-12-29 11:00:00.000
          1          10          20          30 2016-12-29 12:00:00.000
          2          10          20          30 2016-12-29 12:00:00.000
          3          10          20          30 2016-12-29 12:00:00.000
          1          10          20          30 2016-12-29 13:00:00.000
          2          10          20          30 2016-12-29 13:00:00.000
          3          10          20          30 2016-12-29 13:00:00.000
          1          11          21          31 2016-12-29 14:44:09.330
          2          11          21          31 2016-12-29 14:44:09.330
          3          11          21          31 2016-12-29 14:44:09.330
          1          10          20          30 2016-12-29 15:00:00.000
          2          10          20          30 2016-12-29 15:00:00.000
          3          10          20          30 2016-12-29 15:00:00.000
          1          10          20          30 2016-12-29 16:00:00.000
          2          10          20          30 2016-12-29 16:00:00.000
          3          10          20          30 2016-12-29 16:00:00.000
          1          10          20          30 2016-12-29 17:00:00.000
          2          10          20          30 2016-12-29 17:00:00.000
          3          10          20          30 2016-12-29 17:00:00.000
          1          10          20          30 2016-12-29 18:00:00.000
          2          10          20          30 2016-12-29 18:00:00.000
          3          10          20          30 2016-12-29 18:00:00.000
          1          10          20          30 2016-12-29 19:00:00.000
          2          10          20          30 2016-12-29 19:00:00.000
          3          10          20          30 2016-12-29 19:00:00.000
          1          10          20          30 2016-12-29 20:00:00.000
          2          10          20          30 2016-12-29 20:00:00.000
          3          10          20          30 2016-12-29 20:00:00.000
          1          10          20          30 2016-12-29 21:00:00.000
          2          10          20          30 2016-12-29 21:00:00.000
          3          10          20          30 2016-12-29 21:00:00.000
          1          10          20          30 2016-12-29 22:00:00.000
          2          10          20          30 2016-12-29 22:00:00.000
          3          10          20          30 2016-12-29 22:00:00.000
          1          10          20          30 2016-12-29 23:00:00.000
          2          10          20          30 2016-12-29 23:00:00.000
          3          10          20          30 2016-12-29 23:00:00.000
    评论

报告相同问题？

悬赏问题

  • ¥60 版本过低apk如何修改可以兼容新的安卓系统
  • ¥25 由IPR导致的DRIVER_POWER_STATE_FAILURE蓝屏
  • ¥50 有数据，怎么建立模型求影响全要素生产率的因素
  • ¥50 有数据，怎么用matlab求全要素生产率
  • ¥15 TI的insta-spin例程
  • ¥15 完成下列问题完成下列问题
  • ¥15 C#算法问题, 不知道怎么处理这个数据的转换
  • ¥15 YoloV5 第三方库的版本对照问题
  • ¥15 请完成下列相关问题！
  • ¥15 drone 推送镜像时候 purge: true 推送完毕后没有删除对应的镜像,手动拷贝到服务器执行结果正确在样才能让指令自动执行成功删除对应镜像，如何解决？