102.交叉表-优先级处理_表的优先级处理-优快云博客

本文链接：https://blog.youkuaiyun.com/huang714/article/details/88545384

本文介绍了一个复杂的SQL数据库查询过程，通过创建临时表和使用CASE WHEN语句进行数据筛选，实现对房间状态的有效查询。该查询考虑了工作日、周末及特定日期范围内的房间预订情况，确保数据的准确性和实时性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--示例数据
create table a(
qid int,j int,rid nvarchar(10),tagname nvarchar(10),
starttime smalldatetime,endtime smalldatetime,
startweekday int,endweekday int,
startdate smalldatetime,enddate smalldatetime,d int)
insert a  select 1,1 ,'A1','未订','08:00','09:00',1   ,5   ,null       ,null       ,1
union all select 1,2 ,'A1','未订','09:00','10:00',1   ,5   ,null       ,null       ,1
union all select 1,3 ,'A1','未订','10:00','11:00',1   ,5   ,null       ,null       ,1
union all select 1,4 ,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2 
union all select 1,5 ,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,6 ,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,7 ,'A2','未订','08:00','09:00',1   ,5   ,null       ,null       ,1
union all select 1,8 ,'A2','未订','09:00','10:00',1   ,5   ,null       ,null       ,1
union all select 1,9 ,'A2','未订','10:00','11:00',1   ,5   ,null       ,null       ,1
union all select 1,10,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,11,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,12,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2

create table c(id int,qid int,rid varchar(10),consumedate smalldatetime,j int)
insert c  select 1,1,'A1','2005-1-18',1
union all select 1,1,'A2','2005-1-17',8
go

create proc p_qry
@date smalldatetime --要查询的日期
as
set nocount on
declare @week int,@s nvarchar(4000)
--格式化日期和得到星期
select @date=convert(char(10),@date,120)
	,@week=(@@datefirst+datepart(weekday,@date)-1)%7
	,@s=''
select id=identity(int),* into #t
from(
	select top 100 percent
		a.qid,a.rid,a.j,
		tagname=case when c.id is null then a.tagname else N'被订' end,
		starttime=convert(char(5),a.starttime,108),
		endtime=convert(char(5),a.endtime,108)
	from a
		left join c on a.qid=c.qid and a.rid=c.rid and a.j=c.j
			and c.consumedate=@date
	where (@week between a.startweekday and a.endweekday)
		or(@date between a.startdate and a.enddate)
	order by a.qid,a.rid,a.starttime,a.d desc)a

select @s=@s+N',['+rtrim(rid)
	+N']=max(case when qid='+rtrim(qid)
	+N' and rid=N'''+rtrim(rid)
	+N''' then tagname else N'''' end)'
from #t group by qid,rid
exec('
select starttime,endtime'+@s+' 
from #t a
where not exists(
	select * from #t
	where qid=a.qid and rid=a.rid 
		and starttime=a.starttime
		and endtime=a.endtime
		and id)

)