计算日期并同时比较 SQL Server 2008

Question

我有几个datetime列。我需要在 SQL Server 2008 中为每个时间戳计算同一列中有多少个日期时间戳小于每个日期时间戳。

例如：对于第 DateTime1 列中的2016-05-01 14:24:000.00，我需要计算有多少日期时间值比DateTime1列中的值小。

我还需要知道第 DateTime2 列和 3 中同一记录（在同一行中）的日期时间戳比日期时间戳小多少个日期时间戳。

DateTime1                   DateTime2                  DateTime3
----------------------------------------------------------------------------
2016-05-01 13:24:000.00    2016-05-01 15:24:000.00   2016-05-01 16:20:000.00 
2016-05-01 13:30:000.00    2016-05-01 14:21:000.00   2016-05-01 15:10:000.00 
2016-05-01 14:24:000.00    2016-05-01 17:21:000.00   2016-05-01 18:10:000.00

Answer 1

如果我

理解正确，您可以使用rank()：

select t.*,
       rank() over (order by datetime1) as dt1_rank,
       rank() over (order by datetime2) as dt2_rank,
       rank() over (order by datetime3) as dt3_rank
from t ;

根据您希望如何处理绑定值，您可能实际上想要dense_rank()。 此外，您可能希望从排名值中减去 1。

Answer 2

假设我有一个表名[TestTB]有3列日期时间1，日期时间2，日期时间3。我说 CountSmallDateTime1 是"有多少日期时间值比 DateTime1 列中的值小"我说 CountSmallDateTime2 为"有多少日期时间戳小于 DateTime2 列中同一记录（在同一行中）的日期时间戳戳"，相似性，日期时间 3 的 CountSmallDateTime3 。然后我有一个查询您的请求：

SELECT [DateTime1]
  ,[DateTime2]
  ,[DateTime3]
  ,(SELECT COUNT(1) 
    FROM [TestTB] Sub 
    WHERE TB.[DateTime1] >Sub.[DateTime1]) AS CountSmallerDateTime1
  ,(
    CASE WHEN TB.[DateTime2] > TB.[DateTime1]  AND TB.[DateTime2] > TB.[DateTime3] THEN 
        2
        WHEN ( (TB.[DateTime2] <= TB.[DateTime1]  AND TB.[DateTime2] > TB.[DateTime3])
            OR (TB.[DateTime2] > TB.[DateTime1]  AND TB.[DateTime2] <= TB.[DateTime3])) THEN
            1
    ELSE
        0
    END
  ) AS CountSmallerDateTime2,
  (
    CASE WHEN TB.[DateTime3] > TB.[DateTime1]  AND TB.[DateTime3] > TB.[DateTime2] THEN 
        2
        WHEN ( (TB.[DateTime3] <= TB.[DateTime1]  AND TB.[DateTime3] > TB.[DateTime2])
            OR (TB.[DateTime3] > TB.[DateTime1]  AND TB.[DateTime3] <= TB.[DateTime2])) THEN
            1
    ELSE
        0
    END
  ) AS CountSmallerDateTime3 FROM [TestTB] TB

Answer 3

;WITH CTE(DATE1, DATE2, DATE3,RN)
AS
(
SELECT CONVERT(DATETIME , '2016-05-01 13:24:000.00'),   CONVERT(DATETIME,'2016-05-01 15:24:000.00'), CONVERT(DATETIME,'2016-05-01 16:20:000.00'),1 
UNION ALL
SELECT CONVERT(DATETIME , '2016-05-01 13:30:000.00'),   CONVERT(DATETIME,'2016-05-01 14:21:000.00'), CONVERT(DATETIME,'2016-05-01 15:10:000.00'),2 
UNION ALL
SELECT CONVERT(DATETIME , '2016-05-01 14:24:000.00'),   CONVERT(DATETIME,'2016-05-01 17:21:000.00'), CONVERT(DATETIME,'2016-05-01 18:10:000.00'),3
)
SELECT     RANK() OVER (ORDER BY DATE1) -1 AS SAME_COLUMN_DATE1 
         , RANK() OVER (ORDER BY DATE2) -1 AS SAME_COLUMN_DATE2
         , RANK() OVER (ORDER BY DATE3) -1 AS SAME_COLUMN_DATE3  
         , CASE WHEN RN=1 AND DATE1< DATE2 AND DATE1<DATE3 THEN 0
                WHEN RN=1 AND DATE1< DATE2 AND DATE1>DATE3 THEN 1
                WHEN RN=1 AND DATE1> DATE2 AND DATE1<DATE3 THEN 1
                ELSE 2
                SAME_ROW_1
         , CASE WHEN RN=2 AND DATE2< DATE1 AND DATE2<DATE3 THEN 0
                WHEN RN=2 AND DATE2< DATE1 AND DATE2>DATE3 THEN 1
                WHEN RN=2 AND DATE2> DATE1 AND DATE2<DATE3 THEN 1
                ELSE 2
                END SAME_ROW_2
         , CASE WHEN RN=3 AND DATE3< DATE1 AND DATE3<DATE2 THEN 0
                WHEN RN=3 AND DATE3< DATE1 AND DATE3>DATE2 THEN 1
                WHEN RN=3 AND DATE3> DATE1 AND DATE3<DATE2 THEN 1
                ELSE 2
                END SAME_ROW_3           
         FROM CTE ORDER BY RN