如何在分钟值更改时选择行

Question

我有一个名为

Ship(Date datetime,name varchar(50),Type char(1)).

在"发货"表中，"日期"列的数据类型为日期时间。我想在日期列(日期时间数据类型(中的分钟值更改时从发货表中选择行。为此，我使用了以下查询：

;WITH x AS
(
  SELECT Name, Date,Type, rn = ROW_NUMBER() OVER 
  (PARTITION BY Date ORDER BY Date desc)
  FROM Ship
)
SELECT * FROM x WHERE rn = 1

但是期望的输出并没有到来。即将得出的结果是：

Date                         Name     Type
2017-05-08 14:59:13.000      sumit      A
2017-05-08 14:59:23.000      sumit      B
2017-05-08 14:59:33.000      sumit      A
2017-05-08 15:00:05.000      Ajay       B
2017-05-08 15:00:13.000      Deep       G
2017-05-08 15:01:03.000     Suri        D   
2017-05-08 15:01:13.000     Faiz        E

在上面的输出中，当日期列的第二个值发生变化时，这些行也会出现。但是我想在日期列的分钟值发生变化时选择行。任何人都可以解决这个问题吗？

Answer 1

你可以在partition by子句上使用datediff minute

;WITH x AS
(
 SELECT Name, Date,Type, rn = ROW_NUMBER() OVER 
 (PARTITION BY datediff(min,0,[Date]) ORDER BY Date desc)
 FROM Ship
)
SELECT * FROM x WHERE rn = 1

或者它是较短的版本

SELECT TOP 1 WITH TIES
      Name, Date,Type
FROM Ship
ORDER BY ROW_NUMBER() OVER  (PARTITION BY datediff(min,0,[Date]) ORDER BY [date] desc)

Answer 2

您能否在PARTITION BY中添加 DATEPART 以仅在分钟部分中进行更改。

;WITH x AS
(
  SELECT Name, Date,Type, rn = ROW_NUMBER() OVER 
  (PARTITION BY DATEPART(N, Date) ORDER BY Date desc)
  FROM Ship
)
SELECT * FROM x WHERE rn = 1

参考演示：http://rextester.com/YZW84894