SQL实战练习题(3)-求解连续空余座位-leetcode sql 603

问题描述

原始数据

正解

CREATE TABLE cinema(
	seat_id int PRIMARY KEY auto_increment,
	`FREE` boolean
)charset=utf8;
insert into cinema(free) values(1), (0), (0), (0), (1), (1);

-- mysql5.7还没有开窗函数，abs取绝对值，也就是c.seat_id可能大于、等于、小于c2.seat_id, 故最后使用distinct去重.
SELECT DISTINCT c.seat_id 
FROM cinema c join cinema c2 on ABS(c.seat_id - c2.seat_id) = 1 and c.free=1 and c2.free=1
ORDER BY c.seat_id 
;

SELECT DISTINCT c.seat_id 
FROM cinema c, cinema c2 
where abs(c.seat_id - c2.seat_id) = 1 and c.free=1 and c2.free=1
ORDER BY c.seat_id 
;

其它问题

-- 问题: 缺少7和11
SELECT seat_id
FROM cinema c   -- 当前
WHERE c.`FREE` = (
	SELECT c2.`FREE` 
	FROM cinema c2     -- 前
	WHERE c.seat_id+1 = c2.seat_id and c2.`FREE` =1
)