文章/答案/技术大牛

发布

社区首页 >问答首页 >符合我的条件的行的datediff，每一行只满足一次。

问符合我的条件的行的datediff，每一行只满足一次。
EN

Stack Overflow用户

提问于 2019-02-27 11:18:08

回答 4查看 76关注 0票数 0

只有在行有条件的情况下，我才希望在两个日期之间对不同的行执行日期划分。

我的表如下所示，有额外的列(如guid)

Id  | CreateDateAndTime        | condition
---------------------------------------------------------------
1   | 2018-12-11 12:07:55.273  |    with this 
2   | 2018-12-11 12:07:53.550  |    I need to compare this state 
3   | 2018-12-11 12:07:53.550  |    with this 
4   | 2018-12-11 12:06:40.780  |    state 3
5   | 2018-12-11 12:06:39.317  |    I need to compare this state

在这个例子中，我希望在我的选择中有2行，它表示从id 5-3到id 2-1的日期之间的差异。

到目前为止，我带了一个请求，给出了从id 5-3、id 5-1和id 2-1的日期之间的区别：

with t as (
      SELECT TOP (100000) 
          *
      FROM mydatatable

      order by CreateDateAndTime desc) 

      select 
      DATEDIFF(SECOND, f.CreateDateAndTime, s.CreateDateAndTime)  time

      from t f
      join t s on (f.[guid] = s.[guid] )
      where f.condition like '%I need to compare this state%'
      and s.condition like '%with this%'
        and (f.id - s.id) < 0

我的问题是我不能将f.id - s.id设置为一个值，因为其他行可以在我想要进行区分的行之间。

如何使datediff只适用于满足我条件的第一行？

编辑：使其更加清晰

我的条件是一个事件名，我希望计算事件1和事件2之间的时间，例如填充一个名为time的列。

@Salman A answer非常接近我想要的东西，除了当我的事件2没有发生时它就不能工作了(在我最初的例子中)。

例如，在如下的表中，它将使第5行和第2行之间的datediff

Id  | CreateDateAndTime        | condition
---------------------------------------------------------------
1   | 2018-12-11 12:07:55.273  |    with this 
2   | 2018-12-11 12:07:53.550  |    I need to compare this state 
3   | 2018-12-11 12:07:53.550  |    state 3
4   | 2018-12-11 12:06:40.780  |    state 3
5   | 2018-12-11 12:06:39.317  |    I need to compare this state

我修改的代码：

WITH cte AS (
    SELECT id
         , CreateDateAndTime AS currdate
         , LAG(CreateDateAndTime) OVER (PARTITION BY guid ORDER BY id desc ) AS prevdate
         , condition
    FROM t
    WHERE condition IN ('I need to compare this state', 'with this ')
)
SELECT *
     ,DATEDIFF(second, currdate, prevdate) time
FROM cte
WHERE condition = 'I need to compare this state '
and DATEDIFF(second, currdate, prevdate) != 0
order by id desc

sql

sql-server

tsql

datetime

回答 4

Stack Overflow用户

回答已采纳

发布于 2019-02-27 11:31:38

也许您想要将id与最近的较小的id匹配。为此，您可以使用窗口函数：

WITH cte AS (
    SELECT id
         , CreateDateAndTime AS currdate
         , CASE WHEN LAG(condition) OVER (PARTITION BY guid ORDER BY id) = 'with this'
                THEN LAG(CreateDateAndTime) OVER (PARTITION BY guid ORDER BY id) AS prevdate
         , condition
    FROM t
    WHERE condition IN ('I need to compare this state', 'with this')
)
SELECT *
     , DATEDIFF(second, currdate, prevdate)
FROM cte
WHERE condition = 'I need to compare this state'

CASE表达式将匹配this state和with this。如果有错匹配对，则返回NULL。

票数 1

Stack Overflow用户

发布于 2019-02-27 11:20:08

利用解析函数lead()的尝试

    with cte as
(
select 1 as id, '2018-12-11 12:07:55.273' as CreateDateAndTime,'with this' as condition union all
select 2,'2018-12-11 12:07:53.550','I need to compare this state' union all
select 3,'2018-12-11 12:07:53.550','with this' union all
select 4,'2018-12-11 12:06:40.780','state 3' union all
select 5,'2018-12-11 12:06:39.317','I need to compare this state'


) select *, 
    DATEDIFF(SECOND,CreateDateAndTime,lead(CreateDateAndTime) over(order by Id))
    from cte    
     where condition in ('with this','I need to compare this state')

票数 1

Stack Overflow用户

发布于 2019-02-27 11:55:51

理想情况下，您需要LEADIF/LAGIF函数，因为您正在查找条件=“with this”的上一行。由于没有LEADIF/LAGIF，我认为最好的选择是将OUTER/CROSS APPLY与TOP 1一起使用，例如

CREATE TABLE #T (Id INT, CreateDateAndTime DATETIME, condition VARCHAR(28));    
INSERT INTO #T (Id, CreateDateAndTime, condition)
VALUES
    (1, '2018-12-11 12:07:55', 'with this'),
    (2, '2018-12-11 12:07:53', 'I need to compare this state'),
    (3, '2018-12-11 12:07:53', 'with this'),
    (4, '2018-12-11 12:06:40', 'state 3'),
    (5, '2018-12-11 12:06:39', 'I need to compare this state');


SELECT  ID1 = t1.ID,
        Date1 = t1.CreateDateAndTime,
        ID2 = t2.ID,
        Date2 = t2.CreateDateAndTime,
        Difference = DATEDIFF(SECOND, t1.CreateDateAndTime, t2.CreateDateAndTime)
FROM    #T AS t1
        CROSS APPLY
        (   SELECT  TOP 1 t2.CreateDateAndTime, t2.ID
            FROM    #T AS t2
            WHERE   t2.Condition = 'with this'
            AND     t2.CreateDateAndTime > t1.CreateDateAndTime
            --AND       t2.GUID = t.GUID
            ORDER BY CreateDateAndTime
        ) AS t2
WHERE   t1.Condition = 'I need to compare this state';

这意味着：

ID1     Date1                       D2  Date2                       Difference
-------------------------------------------------------------------------------
2       2018-12-11 12:07:53.000     1   2018-12-11 12:07:55.000     2
5       2018-12-11 12:06:39.000     3   2018-12-11 12:07:53.000     74

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/54904271

复制

相似问题

问符合我的条件的行的datediff，每一行只满足一次。
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问符合我的条件的行的datediff，每一行只满足一次。EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问符合我的条件的行的datediff，每一行只满足一次。
EN