如何使用SQL在表中查找N条连续记录 [英] How to find N Consecutive records in a table using SQL

问题描述

我有以下带有示例数据的表定义.在下表中，客户产品 &日期是关键字段

I have the following Table definition with sample data. In the following table, Customer Product & Date are key fields

Table One
Customer   Product    Date         SALE
   X          A       01/01/2010    YES
   X          A       02/01/2010    YES
   X          A       03/01/2010    NO
   X          A       04/01/2010    NO
   X          A       05/01/2010    YES
   X          A       06/01/2010    NO
   X          A       07/01/2010    NO
   X          A       08/01/2010    NO
   X          A       09/01/2010    YES
   X          A       10/01/2010    YES
   X          A       11/01/2010    NO
   X          A       12/01/2010    YES

在上表中，我需要找到N个或> N个连续没有销售的记录，销售值为'NO'例如，如果 N 为 2，则结果集将返回以下内容

In the above table, I need to find the N or > N consecutive records where there was no sale, Sale value was 'NO' For example, if N is 2, the the result set would return the following

     Customer   Product    Date         SALE
       X          A       03/01/2010    NO
       X          A       04/01/2010    NO
       X          A       06/01/2010    NO
       X          A       07/01/2010    NO
       X          A       08/01/2010    NO

有人可以帮助我进行 SQL 查询以获得所需的结果吗?我正在使用 SQL Server 2005.我开始使用 ROW_NUMBER() AND PARTITION 子句但没有运气.感谢您的帮助

Can someone help me with a SQL query to get the desired results. I am using SQL Server 2005. I started playing using ROW_NUMBER() AND PARTITION clauses but no luck. Thanks for any help

推荐答案

您需要将您的表与自身进行匹配，就好像那里有 2 个表一样.因此，您使用两个别名 o1 和 o2 来引用您的表:

You need to match your table against itself, as if there where 2 tables. So you use two aliases, o1 and o2 to refer to your table:

SELECT DISTINCT o1.customer, o1.product, o1.datum, o1.sale
  FROM one o1, one o2
  WHERE (o1.datum = o2.datum-1 OR o1.datum = o2.datum +1)
  AND o1.sale = 'NO' 
  AND o2.sale = 'NO'; 
 customer | product |   datum    | sale 
----------+---------+------------+------
 X        | A       | 2010-01-03 | NO
 X        | A       | 2010-01-04 | NO
 X        | A       | 2010-01-06 | NO
 X        | A       | 2010-01-07 | NO
 X        | A       | 2010-01-08 | NO

请注意，我在 postgresql 数据库上执行了查询——也许语法在 ms-sql-server 上有所不同，也许在别名 'FROM one AS o1' 上，也许你不能以这种方式添加/减去.

Note that I performed the query on an postgresql database - maybe the syntax differs on ms-sql-server, maybe at the alias 'FROM one AS o1' perhaps, and maybe you cannot add/substract in that way.

这篇关于如何使用SQL在表中查找N条连续记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！