【SQL解惑】谜题9：空座位

创建数据源

declare @seat int

set @seat = 1

create table Restaurant ( seat int not null )

while @seat < 1001

begin

insert into Restaurant ( seat ) values ( @seat )

set @seat = @seat + 1

end

select * from Restaurant

解惑一：

（1）将被占座位更新为负数：

update Restaurant

set seat = - seat

from Restaurant

where seat = 1

（2）将被占座位更新为正数：

update Restaurant

set seat = abs ( seat )

from Restaurant

where seat = - 1

解惑三：

（1）创建数据源

declare @seat int

set @seat = 0

create table Restaurant ( seat int not null )

while @seat < 1002

begin

insert into Restaurant ( seat ) values ( @seat )

set @seat = @seat + 1

end

select * from Restaurant

（2）创建临时表

create view Firstseat ( seat )

as select ( seat + 1 )

       from Restaurant

       where ( seat + 1 ) not in ( select seat from Restaurant )

       and ( seat + 1 ) < 1001

go

create view Lastseat ( seat )

  as select ( seat - 1 )

       from Restaurant

       where ( seat - 1 ) not in ( select seat from Restaurant )

       and ( seat - 1 ) > 0

go

（3）假设其中第1，2，3，4，5的空座位被占用，因为需要删除 Restaurant表的数据

delete from Restaurant where seat in ( 1 , 2 , 3 , 4 , 5 )

（4）此时选择Firstseat得出来的结果为1，Lastseat得出来的结果为5

select * from Restaurant

select * from Firstseat

select * from Lastseat

（5）Firstseat的过程：此时的表为 Restaurant的数据为（0...6，7，8，9...1001），因为select的结果为0+1，6+1，7+1...1001+1，其中0+1不在数据表中且小于1001因此被选中，1001+1不在表中由于大于1001因此没有被选中。

（6）Lastseat的过程：此时的表为 Restaurant的数据为（0...6，7，8，9...1001），因为select的结果为0-1，6-1，7-1...1001-1，其中6-1不在数据表中且大于0因此被选中，0-1既不在表中也小于0、1001-1在表中且大于0因此没有被选中。

（7）假设现在 delete from Restaurant where seat in ( 1 , 2 , 3 , 4 , 5,11,12,13,14,15 )

select * from Firstseat 结果为 1，10

select * from Lastseat 结果为5，15

运算逻辑，先看子查询里面的f1.seat；

其中， f1.seat=1，与l2.seat中的5和15比较，筛选出最小的l1.seat=5，再用 f1.seat=1和 l1.seat=5进行计算。

接下来， f1.seat=10，与l2.seat中的5和15比较，筛选出最小的l1.seat=15，再用 f1.seat=10和 l1.seat=15进行计算。

select f1 . seat as start , l1 . seat as finish ,

             (( l1 . seat - f1 . seat ) + 1 ) as available

from Firstseat as f1 , Lastseat as l1

where l1 . seat = ( select MIN ( l2 . seat )

                           from Lastseat as l2

                          where f1 . seat <= l2 . seat )

解惑四：

（1）创建数据源

declare @seat int

set @seat = 0

create table Restaurant ( seat int not null )

while @seat < 1002

begin

insert into Restaurant ( seat ) values ( @seat )

set @seat = @seat + 1

end

select * from Restaurant

（2）假设其中第1，2，3，4，5的空座位被占用，因为需要删除 Restaurant表的数据

delete from Restaurant where seat in ( 1 , 2 , 3 , 4 , 5 )

（3）

select ( r1 . seat + 1 ) as start ,

             ( MIN ( r2 . seat ) - 1 ) as finish

from Restaurant as r1   ---1、获取Restaurant表中的座位

inner join Restaurant as r2 on r2 . seat > r1 . seat   ---2、联合 Restaurant表中的座位，如果表1的数字小于表2的数字则被保留，实际例子：对0来说，保留比0大的所有座位数字，对于6来说，保留比6大的所有数字

group by r1 . seat        ---将表中按 Restaurant表1的 座位进行分组

having ( r1 . seat + 1 ) < min ( r2 . seat )   ---分组内的表1的数字+1与表2的最小值比较，相当于座位数与相邻的一个数字进行比较，相当于一个数+1比向上取最小值的数大的话，则证明该数与邻数并不是连续的。

假设现在 delete from Restaurant where seat in ( 1 , 2 , 3 , 4 , 5,11,12,13,14,15 )

运算逻辑，将表0,6,7,8,9,10,16,17,18....的每个数+1与该数的所有数中的最小值进行比较（即下一个数）进行比较。即0+1与6比较，6+1与7比较，8+1与9比较，10+1与16比较。如果第一个数小于第二个数则证明中间存在空缺，因此则取第一个数+1与第二个数-1得出空缺的范围。

解惑五：

select X . seat_nbr , X . rn ,

         ( seat_nbr - rn ) as avaliable_seat_cnt

from

       ( select seat ,

                   ROW_NUMBER ()

                   over ( order by seat )

         from Restaurant ) as X ( seat_nbr , rn )

where rn <> seat_nbr