时间戳

首页 > 解决方案 > 日期和时间差 (hh:mm:ss) 并根据 where 条件对 Duration 求和

sql - 日期和时间差 (hh:mm:ss) 并根据 where 条件对 Duration 求和

问题描述

在下面的查询中，我需要根据 where 条件获取表中 (HH:MM:SS) 中两个日期时间的持续时间，并且需要对总的 duration.in (hh:mm:ss) 求和，同时执行查询返回错误为：

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

在下面提到的查询中

SELECT CONVERT(TIME, DATEADD(s, SUM(( DATEPART(hh, (B.DateAndTime - A.DateAndTime)) * 3600 ) + ( DATEPART(mi, (B.DateAndTime - A.DateAndTime)) * 60 ) + DATEPART(ss, (B.DateAndTime - A.DateAndTime))), 0)) AS total_time 
FROM (SELECT DateAndTime, ROW_NUMBER() OVER (ORDER BY DateAndTime ASC) AS S FROM TableName WHERE Marker='S' and TagIndex=2 AND a.Val=Val) AS A
INNER JOIN
(SELECT DateAndTime, ROW_NUMBER() OVER (ORDER BY DateAndTime ASC) AS E FROM TableName WHERE Marker='E' and TagIndex=2 AND a.Val=Val) AS B
ON B.E=A.S) AS Duration
    FROM TableName AS a 
    Left join
    TableName a1 on a.Val=a1.Val
      Left join    
    TableName b1 on a.DateAndTime=b1.DateAndTime
     Left join
    TableName b2 on a.DateAndTime=b2.DateAndTime
     Left join
     TableName b3 on a.DateAndTime=b3.DateAndTime
    where a.TagIndex=2 and a.Marker='S' and a1.TagIndex=2 and a1.Marker='E'  and b1.TagIndex=1 and b2.TagIndex=4 and b3.TagIndex=0 and b3.Marker='S'             
     group by a.Val


DateAndTime             Millitm TagIndex    Val Status  Marker
2018-11-22 13:50:23.000 200       0        3000   S      S
2018-11-23 14:50:23.000 200       3         20    S      E     
2019-01-13 09:43:00.000 290       0        3000   S      S
2019-01-14 10:43:00.000 290       3         432   S      E




D1=E-S
D2=E-S
--------
DURATION=D1+D2

数据类型是时间，这就是为什么持续时间达到 23:59 小时。如果差异小时更多，那么它也只计算到 23:59 小时？

标签： sqlsql-server

解决方案

这确实是一个评论，但作为答案，格式更容易理解。

根据SQL Syntax Check Online ，您的查询有错误：

您的 SQL 语法有错误；似乎错误就在附近：'DATEADD（s，SUM（（DATEPART（hh，（B.DateAndTime - A'在第4行）

这是SQL Syntax Check Online
创建的格式化查询 第 4 行是行DATEADD(

SELECT
  CONVERT(
    TIME,
    DATEADD(
      s,
      SUM(
        (
          DATEPART(hh, (B.DateAndTime - A.DateAndTime)) * 3600
        ) + (
          DATEPART(mi, (B.DateAndTime - A.DateAndTime)) * 60
        ) + DATEPART(ss, (B.DateAndTime - A.DateAndTime))
      ),
      0
    )
  ) AS total_time
FROM
  (
    SELECT
      DateAndTime,
      ROW_NUMBER() OVER (
        ORDER BY
          DateAndTime ASC
      ) AS S
    FROM
      TableName
    WHERE
      Marker = 'S'
      and TagIndex = 2
      AND a.Val = Val
  ) AS A
  INNER JOIN (
    SELECT
      DateAndTime,
      ROW_NUMBER() OVER (
        ORDER BY
          DateAndTime ASC
      ) AS E
    FROM
      TableName
    WHERE
      Marker = 'E'
      and TagIndex = 2
      AND a.Val = Val
  ) AS B ON B.E = A.S
) AS Duration
FROM
  TableName AS a
  Left join TableName a1 on a.Val = a1.Val
  Left join TableName b1 on a.DateAndTime = b1.DateAndTime
  Left join TableName b2 on a.DateAndTime = b2.DateAndTime
  Left join TableName b3 on a.DateAndTime = b3.DateAndTime
where
  a.TagIndex = 2
  and a.Marker = 'S'
  and a1.TagIndex = 2
  and a1.Marker = 'E'
  and b1.TagIndex = 1
  and b2.TagIndex = 4
  and b3.TagIndex = 0
  and b3.Marker = 'S'
group by
  a.Val

推荐阅读