如何使用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屋!