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

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

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

---

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

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.

---

A different approach, inspired by munchs last line.

Get - for a given date the first date with YES later than that, and the last date with YES earlier than that. These form the boundary, where our dates shall fit in.

SELECT (o1.datum),
    MAX (o3.datum) - MIN (o2.datum) AS diff
FROM one o1, one o2, one o3 
WHERE o1.sale = 'NO'
AND o3.datum <
    (SELECT MIN (datum) 
    FROM one 
    WHERE datum >= o1.datum 
    AND SALE = 'YES') 
AND o2.datum > 
    (SELECT MAX (datum) 
    FROM one 
    WHERE datum <= o1.datum 
    AND SALE = 'YES') 
GROUP BY o1.datum 
HAVING MAX (o3.datum) - MIN (o2.datum) >= 2
ORDER BY o1.datum;

Maybe it needs some kind of optimization, because table one is 5 times involved in the query. :)

---

Thanks to everyone for posting your solution. Thought, I would also share my solution with everyone. Just as an FYI, I received this solution from another SQL Server Central forum member. I am definitely not going to take credit for this solution.

DECLARE @CNT INT
SELECT @CNT = 3

SELECT * FROM
(
  SELECT
    [Customer], [Product], [Date], [Sale], groupID, 
    COUNT(*) OVER (PARTITION BY [Customer], [Product], [Sale], groupID) AS groupCnt
  FROM
  (
    SELECT
      [Customer], [Product], [Date], [Sale],
      ROW_NUMBER() OVER (PARTITION BY [Customer], [Product] ORDER BY [Date])
      - ROW_NUMBER() OVER (PARTITION BY [Customer], [Product], [Sale] ORDER BY [Date]) AS groupID
    FROM
      [TableSales]
  ) T1
) T2
WHERE
  T2.[Sale] = 'NO' AND T2.[groupCnt] >= @CNT

链接地址: http://www.djcxy.com/p/45558.html

上一篇: 如何确定bjam中cxxflags的值？ 或追加到它？

下一篇: 如何在使用SQL的表中找到N个连续记录