sql : get consecutive group 'n' rows (could be inbetween)

Question

下面是我的剧院表:

create table theater
(
   srno integer, 
   seatno integer, 
   available boolean
);

insert into theater
values
(1, 100,true),
(2, 200,true),
(3, 300,true),
(4, 400,false),
(5, 500,true),
(6, 600,true),
(7, 700,true),
(8, 800,true);

我想要一个 sql，它应该将输入作为“n”并返回第一个“n”个连续的可用座位，比如

• 如果 n = 2 输出应该是 100,200
• 如果 n = 4 输出应该是 500,600,700,800

注意:我正在尝试为 postgres 9.3 构建查询

Answer 1

在 SQL-Server 中，您可以按以下方式执行此操作:

DECLARE @num INT = 4

;WITH cte AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt) pt  FROM
(
    SELECT tt.*
        ,(SELECT COUNT(srno) FROM theater t WHERE available <> 'true' and srno < tt.srno) AS cnt
    FROM  theater tt
    WHERE available = 'true'
) t1
)
SELECT TOP (SELECT @num) srno, seatno, available
FROM cte
WHERE pt >= @num

输出

srno    seatno  available
5       500 true
6       600 true
7       700 true
8       800 true