sqlserver 连续座位筛选

最新推荐文章于 2024-02-04 14:30:48 发布

Fergus awsl

最新推荐文章于 2024-02-04 14:30:48 发布

阅读量323

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/weixin_43650411/article/details/101217602

版权

本文详细介绍了在SQLServer中如何进行连续座位的筛选操作，探讨了相关查询技巧，包括利用ROW_NUMBER()函数和自连接等方法，帮助数据库管理员高效处理座位排序和筛选问题。

摘要由CSDN通过智能技术生成

在这里插入图片描述


/* 余票查询 */
if (exists (select * from master.dbo.sysdatabases where name='LeetCode'))
	begin
		use master
		drop database LeetCode
		select 1
	end
create database LeetCode
use LeetCode
go

if (exists (select * from sys.objects where name='cinema'))
	drop table cinema
create table cinema
(
	seat_id int identity primary key,
	free bit not null
)
insert into cinema values
	(1), (0), (1), (1), (1), ('1'), ('0'), ('0'), ('1'), ('1')   -- bit！
select * from cinema
go

select * from cinema
	where free=1
	order by seat_id  -- 如何判断一串数字是否连续?删除非连续的数字?

select distinct a.seat_id from
	(select * from cinema where free=1) a
	join (select * from cinema where free=1) b
	on a.seat_id=b.seat_id-1 or a.seat_id=b.seat_id+1
	
select distinct a.seat_id from
	(select * from cinema where free=1) a
	join (select * from cinema where free=1) b
	on abs(a.seat_id-b.seat_id)=1

select distinct a.seat_id  -- 自连接；速度最快；最佳格式
	from cinema a
	join cinema b
	on abs(a.seat_id-b.seat_id)=1
	and a.free=1 and b.free=1
	order by a.seat_id