同一列 SQL 中 2 个日期的 DateDiff [英] DateDiff of 2 dates in the same column SQL

问题描述

使用 SQL Server 2005 我需要获取同一列中 2 个日期的 datediff，表格如下所示:

Using SQL Server 2005 I need to get the datediff of 2 dates that are in the same column, table looks like this:

OrderNo    OpNo    ResType    LoadedStartDate
-----------------------------------------------------
12345      1       PAINT      2014-05-01 00:00:00.000
12345      2       PAINT      2014-05-02 00:00:00.000
12345      3       PAINT      2014-05-03 00:00:00.000
12345      4       ASMB       2014-05-04 00:00:00.000
67890      1       PAINT      2014-05-02 00:00:00.000
67890      2       PAINT      2014-05-03 00:00:00.000
67890      3       PAINT      2014-05-04 00:00:00.000
67890      4       ASMB       2014-05-05 00:00:00.000

我需要获取 OpNo 1 和 OpNo 4 的日期差异，其中它们的订单号相同.OpNo 将始终是 1 和 4 作为我要比较的那些，ResType 也是如此.

I need to get the date diff of OpNo 1 and OpNo 4 where they are the same order number. The OpNo will always be 1 and 4 as the ones I am trying to compare, as will the ResType.

输出应该是这样的:

OrderNo    Difference
----------------------
12345      3
67890      3

感谢您的帮助:)

推荐答案

只需将表格加入自身:

SELECT t1.OrderNo,DATEDIFF(day,t1.LoadedStartDate,t2.LoadedStartDate)
FROM UnnamedTableFromQuestion t1
       INNER JOIN
     UnnamedTableFromQuestion t2
       on
         t1.OrderNo = t2.OrderNo
WHERE t1.OpNo = 1 and
      t2.OpNo = 4

这篇关于同一列 SQL 中 2 个日期的 DateDiff的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！