問題描述

我正在使用 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 確定“失序"記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！