我正在尝试使用 SQL Server 2012 LAG 函数编写查询,以从我的 [Order] 表中检索数据,其中一行和上一行之间的日期时间差小于 2 分钟。
我期待的结果是
1234 April, 28 2012 09:00:00
1234 April, 28 2012 09:01:00
1234 April, 28 2012 09:03:00
5678 April, 28 2012 09:40:00
5678 April, 28 2012 09:42:00
5678 April, 28 2012 09:44:00
但我看到了
1234 April, 28 2012 09:00:00
1234 April, 28 2012 09:01:00
1234 April, 28 2012 09:03:00
5678 April, 28 2012 09:40:00
5678 April, 28 2012 09:42:00
5678 April, 28 2012 09:44:00
91011 April, 28 2012 10:00:00
不应返回最后一行。这是我尝试过的:SQL Fiddle
有人有想法吗?
首先,我添加了一行来向您展示其他人的答案不起作用的地方,但他们现在删除了它。
现在是我的查询中的逻辑。您说您希望每一行都在另一行的两分钟内。这意味着您不仅要向后看,还要使用 LEAD() 向前看。在您的查询中,您在上次时间为 NULL 时返回,因此它只返回每个 OrderNumber 的第一个值,无论它是对还是错。碰巧,每个订单号的第一个值需要返回,直到您到达它损坏的最后一个订单号。我的查询纠正了这一点,应该适用于您的所有数据。
CREATE TABLE [Order]
(
OrderNumber VARCHAR(20) NOT NULL
, OrderDateTime DATETIME NOT NULL
);
INSERT [Order] (OrderNumber, OrderDateTime)
VALUES
('1234', '2012-04-28 09:00:00'),
('1234', '2012-04-28 09:01:00'),
('1234', '2012-04-28 09:03:00'),
('5678', '2012-04-28 09:40:00'),
('5678', '2012-04-28 09:42:00'),
('5678', '2012-04-28 09:44:00'),
('91011', '2012-04-28 10:00:00'),
('91011', '2012-04-28 10:25:00'),
('91011', '2012-04-28 10:27:00');
with Ordered as (
select
OrderNumber,
OrderDateTime,
LAG(OrderDateTime,1) over (
partition by OrderNumber
order by OrderDateTime
) as prev_time,
LEAD(OrderDateTime,1) over (
partition by OrderNumber
order by OrderDateTime
) as next_time
from [Order]
)
SELECT OrderNumber,
OrderDateTime
FROM Ordered
WHERE DATEDIFF(MINUTE,OrderDateTime,next_time) <= 2 --this says if the next value is less than or equal to two minutes away return it
OR DATEDIFF(MINUTE,prev_time,OrderDateTime) <= 2 --this says if the prev value is less than or equal to 2 minutes away return it
结果(记得我加了一行):
OrderNumber OrderDateTime
-------------------- -----------------------
1234 2012-04-28 09:00:00.000
1234 2012-04-28 09:01:00.000
1234 2012-04-28 09:03:00.000
5678 2012-04-28 09:40:00.000
5678 2012-04-28 09:42:00.000
5678 2012-04-28 09:44:00.000
91011 2012-04-28 10:25:00.000
91011 2012-04-28 10:27:00.000