sql - T-SQL 将动态行添加到结果集中

Question

我有两个表:

• 房间
• 契约(Contract)(租赁契约(Contract))
• 关系:契约(Contract) n-1 房间

我获取用户输入startDate和endDate。根据该间隔，我查询哪些契约(Contract)与房间相关。相关手段:

• 在给定的时间间隔内正在出租房间
• 房间在给定的时间间隔内开始出租
• 房间在给定的时间间隔内停止出租

我的查询是:

SELECT Room.id,
       RentContract.activeon,
       RentContract.expireson
FROM RentContract
INNER JOIN Room ON RentContract.roomid = Room.id
WHERE (RentContract.new_activeon >= @startDate
       OR RentContract.new_activeon IS NULL)
  AND (RentContract.new_expireson <= @endDate
       OR RentContract.new_expireson IS NULL)

现在，要求我另外显示这些房间的非出租间隔。由于我的数据库中没有该内容，我想我需要插入某种动态行，我将在同一列表中显示它们。此外，我将在结果(状态)中显示一个附加列，其中实际契约(Contract)将显示“已占用”，而“动态”行将显示“空”。

例如，用户输入为:startDate = 01.05.2016，endDate = 01.07.2016

我现在的结果是:

我想要的结果是:

所以我实际上需要用数据库记录或动态记录“填充”整个输入间隔

<小时/>

@Rhumborl你的解决方案几乎对我有用!还剩下一个小细节:

我得到了一些结果，契约(Contract)在一个月的 2 日开始，所以我想我需要在那一天有一个“空”条目。例如。 2016 年 1 月 1 日至 2016 年 1 月 1 日 空。这是我的初始结果的一些子集以及我从您的解决方案中获得的结果(我将特定场景标记为黄色):

初始查询:

Rhumborl 的查询:

是否对您的查询进行了一些调整来解决这个问题？

Answer 1

您需要几个 CTE 才能解决这个问题。基本思想是获取您已经拥有的所有占用时间，然后使用该结果集中的日期来查找每个房间的间隙。

首先是完整的查询:

declare @startDate smalldatetime = '20160501',
        @endDate smalldatetime = '20160701'

; with occupieds as (
    SELECT Room.id,
           RentContract.activeon,
           RentContract.expireson,
           'Occupied' as [State],
           -- get ordering of contract for each rooms
           row_number() over (partition by roomid order by activeon) SortOrder
    FROM RentContract
    INNER JOIN Room ON RentContract.roomid = Room.id
    WHERE (RentContract.activeon >= @startDate
           OR RentContract.activeon IS NULL)
      AND (RentContract.expireson <= @endDate
           OR RentContract.expireson IS NULL)
),
empties as (
    select o1.id, o1.expireson + 1 as activeon, o2.activeon - 1 as expireson, 'Empty' as [State] from occupieds o1
        inner join occupieds o2 on o1.id = o2.id and o1.SortOrder = o2.SortOrder - 1
),
extremes as (
    select id, @startDate as activeon, min(activeon) - 1 as expireson, 'Empty' as [State] from occupieds group by id
        having min(activeon) > @startDate
    union all
    select id, max(expireson) + 1 as activeon, @endDate as expireson, 'Empty' as [State] from occupieds group by id
        having max(expireson) < @enddate
)
select id, activeon, expireson, [State] from occupieds
    union all
select id, activeon, expireson, [State] from empties
    union all
select id, activeon, expireson, [State] from extremes
order by id, activeon

让我们来分解一下

第 1 步 - 占用的房间

这与您当前的查询几乎相同。唯一的补充是我们将使用 row_number()获取每个房间的契约(Contract)订单。我们将在下一步中使用它。

SELECT Room.id,
       RentContract.activeon,
       RentContract.expireson,
       'Occupied' as [State],
       -- get ordering of contract for each rooms
       row_number() over (partition by roomid order by activeon) SortOrder
FROM RentContract
INNER JOIN Room ON RentContract.roomid = Room.id
WHERE (RentContract.activeon >= @startDate
       OR RentContract.activeon IS NULL)
  AND (RentContract.expireson <= @endDate
       OR RentContract.expireson IS NULL)

这给出了以下内容

id  | activeon            | expireson           | State    | SortOrder
1   | 2016-05-01 00:00:00 | 2016-05-31 00:00:00 | Occupied | 1
1   | 2016-06-15 00:00:00 | 2016-06-25 00:00:00 | Occupied | 2
2   | 2016-05-01 00:00:00 | 2016-07-01 00:00:00 | Occupied | 1

第 2 步 - 契约(Contract)之间的空房间

现在我们有了每个房间的契约(Contract)及其出现的顺序，我们可以在一个契约(Contract)和下一个契约(Contract)之间使用自连接来计算出其为空的日期范围。因此，选择该行，然后在与之前的 SortOrder 相同的 roomid 上将其自身连接起来。在上表中，第 1 行将连接到第 2 行。这为我们提供了开始日期(第 1 行的到期日期)和结束日期(第 2 行的事件日期)。我们只是添加/减去一天，这样它们就不会重叠:

select o1.id, o1.expireson + 1 as activeon, o2.activeon - 1 as expireson, 'Empty' as [State] from occupieds o1
    inner join occupieds o2 on o1.id = o2.id and o1.SortOrder = o2.SortOrder - 1

第 3 步 - 管理范围开头和结尾的间隙

最后一步是如果房间在范围开始时是空的 - 这不会包含在第 1 步中，因为第一个契约(Contract)没有“前一个”行。

为此，我们只需要找到最早占用的日期并将其用作空期的到期日期。我们还检查这是在开始日期之后，这样我们就不会得到实际占用的 ROM 在同一天开始和结束的条目。

同样适用于结束日期 - 找到最大到期日期并使用 endDate 作为结束日期:

select id, @startDate as activeon, min(activeon) - 1 as expireson, 'Empty' as [State] from occupieds group by id
    having min(activeon) > @startDate
union all
select id, max(expireson) + 1 as activeon, @endDate as expireson, 'Empty' as [State] from occupieds group by id
    having max(expireson) < @enddate

第 4 步 - 将所有内容放在一起

我们现在拥有所需的所有条目，因此我们只需将三个结果集合并在一起即可:

select id, activeon, expireson, [State] from occupieds
    union all
select id, activeon, expireson, [State] from empties
    union all
select id, activeon, expireson, [State] from extremes
order by id, activeon

对于空的和极端的 CTE，您可以将它们作为最终联合中的子查询，但为了清楚起见，我将它们分开

; with occupieds as (
    SELECT Room.id,
           RentContract.activeon,
           RentContract.expireson,
           'Occupied' as [State],
           row_number() over (partition by roomid order by activeon) SortOrder
    FROM RentContract
    INNER JOIN Room ON RentContract.roomid = Room.id
    WHERE (RentContract.activeon >= @startDate
           OR RentContract.activeon IS NULL)
      AND (RentContract.expireson <= @endDate
           OR RentContract.expireson IS NULL)
)
select id, activeon, expireson, [State] from occupieds
    union all
select o1.id, o1.expireson + 1 as activeon, o2.activeon - 1 as expireson, 'Empty' as [State] from occupieds o1
    inner join occupieds o2 on o1.id = o2.id and o1.SortOrder = o2.SortOrder - 1
    union all
select id, @startDate as activeon, min(activeon) - 1 as expireson, 'Empty' as [State] from occupieds group by id
    having min(activeon) > @startDate
    union all
select id, max(expireson) + 1 as activeon, @endDate as expireson, 'Empty' as [State] from occupieds group by id
    having max(expireson) < @enddate
order by id, activeon