T-SQL 确定“失序"记录 [英] T-SQL to determine "out of sequence" records

问题描述

我正在使用 SQL 服务器，我需要确定乱序"的记录；来自一张桌子.

I am using SQL server and I need to determine records that are "out of sequence" from a table.

我会通过一个例子来解释.我有以下表结构:

I'll explain through an example. I have the following table structure:

OrderNumber OrderStatus EventDateTime
0001522989  22          2014-04-14 05:49:25.4414243
0001522989  26          2014-04-14 05:51:16.7047485
0001522989  23          2014-04-14 05:51:17.8602798
0001522990  23          2014-04-14 05:51:19.9603575
0001522990  24          2014-04-14 05:52:06.5803494
0001522990  24          2014-04-14 05:52:06.5803494

现在我需要生成无序"发送的 OrderNumber 列表.所以在这个例子中，列表将只包含一个值:0001522989".

Now I need to produce a list of OrderNumbers that were sent "out of order". So in this example, the list will contain only one value: "0001522989".

订单 0001522990 以正确的顺序发送(首先是状态 23，然后是状态 24，然后是状态 24(这不算作无序")).

Order 0001522990 was sent in the correct sequence (first status 23, then status 24 and then again status 24 (this doesn't count as "out of sequence")).

订单 0001522989 的发送顺序不正确(先是状态 22，然后是状态 26，然后是状态 23).

Order 0001522989 was not sent in the correct sequence (first status 22, then status 26 and then status 23).

知道如何实现这一点吗?

Any idea on how I can accomplish this?

我添加了订单连续两次发送相同状态的可能性(这不应算作乱序")

I added the possibility of an order to send out the same status twice in a row (this shouldn't count as "out of sequence")

提前致谢.

推荐答案

从 SQL Server 2008 开始...

In SQL Server 2008 onwards...

SELECT
  OrderNumber
FROM
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY OrderStatus, EventDateTime  )   AS sequenceCorrect,
    ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY              EventDateTime)   AS sequenceActual
  FROM
    yourTable
)
  AS yourTableSequenced
WHERE
  sequenceCorrect <> sequenceActual
GROUP BY
  OrderNumber
ORDER BY
  OrderNumber

糟糕，我忘记了 WHERE 子句，现在应该可以工作了 ;)

EDIT : Oops, I forgot the WHERE clause, should work now ;)

这篇关于T-SQL 确定“失序"记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！