首先，您需要修改您的设计，您打算如何存储Hall 6是Hall 4 + 5组合的信息

一种简单的方法是在Halls表中添加另一列来指示这一点。示例

create table Halls
(
  id int,
  hallName varchar(10),   
  hallType varchar(10),  
  numSeats int,
  status varchar(10),
  combine_id int
);

insert into Halls values
(4,   'Hall 4',      'Normal',     60,       'active', 6),
(5,   'Hall 5',      'Normal',     80,       'active', 6),
(6,   'Hall 4+5',    'Normal',    140,       'active', null);

一旦放置到位，您需要平移Bookings以处理合并的大厅。这是由CTE BookingData执行的。如果Hall 4或Hall 5是pending或confirmed，它将为Hall 6创建行。同样，反过来也是如此。当Hall 6为Booked时，Hall 4和Hall 5将不可用。

在此之后，只需简单地旋转数据

解决方案：

DECLARE @date date = '2022-09-26';

with BookingData as
(
  select b.hallId, b.time, b.status, beginDate
  from   Bookings b

  union all

  select hallId = h.combine_id, b.time, 
         status = case when b.status in ('pending', 'confirmed')
                       then 'unavailable'
                       else 'available'
                       end, 
         beginDate
  from   Bookings b
         inner join Halls h on b.hallId = h.id
  where  h.combine_id is not null

  union all

  select hallId = h.id, b.time, 
         status = case when b.status in ('pending', 'confirmed')
                       then 'unavailable'
                       else 'available'
                       end,   
         beginDate
  from   Bookings b
       inner join Halls h on b.hallId = h.combine_id
  where  h.combine_id is not null
)
SELECT  id, 
        hallName, 
        hallType, 
        [morning]   = isnull([morning], 'available'), 
        [afternoon] = isnull([afternoon], 'available'), 
        [evening]   = isnull([evening], 'available')
FROM
(
    SELECT  h.id, h.hallName, h.hallType, t.timeSlot,
            status = case when b.status in ('pending', 'confirmed') 
                          then 'Booked' 
                          when b.status in ('cancelled')
                          then 'unavailable'
                          when b.status in ('unavailable')
                          then b.status
                          else NULL
                          end
    FROM    Halls h 
            LEFT JOIN BookingData b  ON  b.hallId     = h.id 
                                     and b.beginDate  = @date
            OUTER APPLY
            (
                select timeSlot = 'morning'   where b.time in ('morning', 'full time')
                union all
                select timeSlot = 'afternoon' where b.time in ('afternoon', 'full time')
                union all
                select timeSlot = 'evening'   where b.time in ('evening', 'full time')
            ) t
) D
PIVOT
(
    MAX (status)
    FOR timeSlot in ( [morning], [afternoon], [evening] )
) P

db<>fiddle demo

您的问题非常复杂，因为表没有正确地实现“组合大厅”的规范化。我们需要使用字符串操作分离Hall 4+5，然后将提取的信息存储在某个位置。以下是我能想到的问题：

with
 composite_halls as
  (select *,
    substring(hallName, charindex(' ', hallName) + 1,
    charindex('+', hallName) - charindex(' ', hallName) - 1) as id1,
    right(hallName, len(hallName) - charindex('+', hallName)) as id2,
    (null) as id3
  from halls where charindex('+', hallName) > 0),
 singular_halls as
  (select *,
   (select ch.id2 from composite_halls as ch where ch.id1 = h.id) as id1,
   (select ch.id1 from composite_halls as ch where ch.id2 = h.id) as id2,
   (select ch.id from composite_halls as ch where ch.id1 = h.id or ch.id2 = h.id) as id3
  from halls as h where charindex('+', hallName) = 0),
 all_halls as (
  select * from singular_halls
  union
  select * from composite_halls),
 valid_bookings as (
  select * from bookings where status = 'confirmed'
        and beginDate >= '2022-09-26' and endDate <= '2022-09-26')
select *,
  (case when exists (
          select *
          from valid_bookings as vb
          where (time = 'morning' or time = 'full time')
             and (vb.hallId = h.id
              or (vb.hallId = h.id1 and h.id3 is null)
              or (vb.hallId = h.id2 and h.id3 is null)
              or vb.hallId = h.id3))
    then 'booked'
    else 'available'
    end) as morning
  from all_halls as h

你可以在小提琴上试一下：https://dbfiddle.uk/baQyI1Y7

它看起来有点可恶，我相信其他人可以写出更短的查询。

上面的查询使用了4个CTE，其中3个处理提取相关“组合大厅”的e1d1d1。composite_halls将从hallName中提取数字，并将它们放入id1和id2中。singular_halls将把对方的id放在id1或id2中，将其组合放在id3中。all_halls将两个结果合并为一个。valid_bookings筛选给定时间范围的bookings，并且仅获取confirmed``bookings。

下一步，它检查all_halls条目中是否有任何bookings‘hallId对应于id、id1、id2、id3中的任何一个。到目前为止，它只处理morning预订，并且只提供booked和available状态。您可以通过添加更多case when来扩展它以提供unavailable，以专门处理id1到id3。您可以通过更改case when子句中的time参数，以相同的方式处理after noon和evening。

我不认为上面的查询对于实时应用程序来说性能足够好。特别是我们逐行运作的事实，是从“合并”的大厅中提取相关的大厅。

我相信，如果你把table正常化一点，事情会容易得多。我觉得这就像用TSQL做EXCEL一样，这看起来不太对。