http://www.sqler.com/138840 참고

with res as

(     

select  1 seat_no  union all

select  2 dd union all

select  4 dd union all

select  5 dd union all

select  7 dd union all

select  8 dd union all

select  9 dd union all

select  10 dd union all

select  12 dd union all

select  15 dd union all

select  16 dd union all

select  17 dd union all

select  18 dd union all

select  19 dd  )  

 select   MIN(seat_no) std_dt, MAX(seat_no) end_dt , COUNT(*) cnt

 from ( 

             select  seat_no

                   ,   ROW_NUMBER() over (  order by  seat_no ) idx     

                   ,   10000 - seat_no  no               

             from  res

        )  r

group by  idx + no      

having   COUNT(*) >= 3              

order  by  idx + no  desc

sql server 2012 이상이라면,

아래와 같이 가능할겁니다.

-----------------

WITH SEAT_INFO AS

  (SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 6 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 8 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 6 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 5 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 5 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 8 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE )

SELECT THEATER_IDX,

       SECTION_ROW,

       SEAT_NO START_SEAT_NO,

       SEAT_NO + 2 END_SEAT_NO

  FROM (

        SELECT THEATER_IDX,

               SECTION_ROW,

               SEAT_NO,

               COUNT(CASE SEAT_USE WHEN 'N' THEN 1 END) OVER (PARTITION BY THEATER_IDX,SECTION_ROW ORDER BY SEAT_NO ROWS BETWEEN CURRENT ROW AND 2  FOLLOWING) NEIGHBOR_CNT

          FROM SEAT_INFO   

       ) A

 WHERE NEIGHBOR_CNT = 3

-------------

단 2 FOLLOWING과 같이 숫자를 직접입력해야 합니다. @negihbor 같은 변수로 처리가 안되네요.

ORDER BY ~ ROWS ~는 2012부터 지원된다는것도 문제구요.

해서 2008버전에서는 아래와 같이 해보면 어떻까 싶네요.

-------------------------

DECLARE @NEIGHBOR_CNT INT

SET @NEIGHBOR_CNT = 3;

WITH SEAT_INFO AS

  (SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 6 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'A' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 8 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'B' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 6 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 1 THEATER_IDX, 'C' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 5 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'A' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 5 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 8 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'B' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 1 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 2 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 3 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 4 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 5 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 6 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 7 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 8 SEAT_NO, 'Y' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 9 SEAT_NO, 'N' SEAT_USE UNION ALL

   SELECT 2 THEATER_IDX, 'C' SECTION_ROW, 10 SEAT_NO, 'N' SEAT_USE )

SELECT A.THEATER_IDX,

       A.SECTION_ROW,

       A.SEAT_NO START_SEAT_NO,

       A.SEAT_NO + @NEIGHBOR_CNT - 1 END_SEAT_NO

  FROM (SELECT A.THEATER_IDX,

               A.SECTION_ROW,

               A.SEAT_NO,

               B.SEAT_NO RANGE_SEAT_NO,

               B.SEAT_USE

          FROM SEAT_INFO A LEFT OUTER JOIN SEAT_INFO B ON (A.THEATER_IDX = B.THEATER_IDX AND A.SECTION_ROW = B.SECTION_ROW AND B.SEAT_NO BETWEEN A.SEAT_NO AND A.SEAT_NO + @NEIGHBOR_CNT - 1)

       ) A

 GROUP BY A.THEATER_IDX,A.SECTION_ROW,A.SEAT_NO

HAVING COUNT(CASE A.SEAT_USE WHEN 'N' THEN 1 END) = @NEIGHBOR_CNT

--------------