sql题：n个人一起去看电影，准备预定电影票横向坐一起，从这么多排座位中，找出连续n个空位的全部组合，结果输出座位组合情况——oracle数据库

n个人情况，请直接跳转到总结查看

例：n=4

表结构：

表名SQL2013

解题思路 

-- 第一步：使用窗口函数 row_number 对 SQL2013 进行排序，seat - row_number() over() as k ，如果座位未预定且连续，这组 k 值应该是相等的。
SELECT seat,rowids,seat-ROW_NUMBER() OVER(ORDER BY seat) AS K FROM SQL2013 WHERE status='未预订'

-- 第二步：将第一步查询去除rowids字段的结果作为第二步的查询表，同时将此表按照 k 进行分组查询，并且筛选出大于 2 的 k 值
SELECT k FROM 
(SELECT seat,seat-ROW_NUMBER() OVER(ORDER BY seat) AS K FROM SQL2013 WHERE status='未预订') 
GROUP BY k HAVING COUNT(*)>=2

-- 第三步：将第一步的查询结果作为第三步的查询表，并筛选出k值等于第二步查询出的k值
SELECT seat,rowids FROM 
(SELECT seat,rowids,SEAT-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订')
WHERE k IN 
(SELECT k FROM 
(SELECT seat,SEAT-ROW_NUMBER() OVER(ORDER BY seat) AS K FROM SQL2013 WHERE status='未预订') 
GROUP BY k HAVING COUNT(*)>=2)

-- 第四步：将第三步的查询结果作为第四步查询表并进行自左连接，筛选出连续的rowids相等，并找到seat间隔3个的seat，分别记为seat和eseat
SELECT S.seat seat,E.seat eseat FROM 
(SELECT seat,rowids,k FROM (SELECT seat,rowids,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') WHERE k IN (SELECT k FROM (SELECT seat,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') GROUP BY k HAVING COUNT(*)>=2)) S 
LEFT JOIN 
(SELECT seat,rowids,k FROM (SELECT seat,rowids,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') WHERE k IN (SELECT k FROM (SELECT seat,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') GROUP BY k HAVING COUNT(*)>=2)) E 
ON E.seat=S.seat+3 AND S.rowids=E.rowids AND S.k=E.k

-- 第五步：讲第四部的查询结果作为第五步的查询表，筛选出eseat不为空的数据进行组合得到最终结果
SELECT seat||'~'||eseat 座位组合情况 FROM 
(SELECT S.seat seat,E.seat eseat FROM (SELECT seat,rowids,k FROM (SELECT seat,rowids,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') WHERE k IN (SELECT k FROM (SELECT seat,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') GROUP BY k HAVING COUNT(*)>=2)) S LEFT JOIN (SELECT seat,rowids,k FROM (SELECT seat,rowids,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') WHERE k IN (SELECT k FROM (SELECT seat,seat-ROW_NUMBER() OVER(ORDER BY seat) AS k FROM SQL2013 WHERE status='未预订') GROUP BY k HAVING COUNT(*)>=2)) E ON E.seat=S.seat+3 AND S.rowids=E.rowids AND S.k=E.k) 
WHERE eseat IS NOT NULL

查询结果 ：

 

总结 

为n情况下只需把上述解题思路第五步中的sql结尾的E.seat=S.seat+3中将3替换成n-1即可。