T-SQL 确定“失序"记录

T-SQL to determine quot;out of sequencequot; records(T-SQL 确定“失序记录)
本文介绍了T-SQL 确定“失序"记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我正在使用 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 确定“失序"记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Creating table with T-SQL - can#39;t see created tables in Object explorer(使用 T-SQL 创建表 - 在对象资源管理器中看不到创建的表)
How to check if VARCHAR strings are (not) hexadecimal?(如何检查 VARCHAR 字符串是否为(非)十六进制?)
Arithmetic overflow error converting IDENTITY to data type int(将 IDENTITY 转换为数据类型 int 的算术溢出错误)
Where clause if there are multiple of the same ID(如果有多个相同的 ID,Where 子句)
Azure SQL: Invalid Object Name using Powershell#39;s quot;Invoke-sqlcmdquot; on Adventureworks(Azure SQL:使用 Powershell 的“Invoke-sqlcmd的无效对象名称在 Adventureworks 上)
How to Convert Table Data into xml format using sql with multiple sub nodes(如何使用具有多个子节点的sql将表数据转换为xml格式)