基于所选值的单个或多个 INSERT

Question

我们正在将网球场的预订信息从 SQL 数据库中提取到一个简单的结果表中，以帮助我们了解球场使用情况。除了持续超过一个小时的预订之外，这非常简单。

目前，每个预订都会在我们的结果表中占据一行。每行包含开始时间、持续时间和法庭编号。我们希望将此表直接映射到电子表格或数据透视表中，以便我们可以了解我们的球场被预订了多少小时以及一天中的哪些时间。

目前，我们的 SQL 查询类似于：

INSERT INTO Results (year, month, day, hour, duration, court)
SELECT DATEPART (yy, b.StartDateTime),
       DATEPART (mm, b.StartDateTime),
       DATEPART (dd, b.StartDateTime),
       DATEPART (hh, b.StartDateTime),
       a.Duration,
       a.Court
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID

我们的问题是，持续时间为 2、3 或更多小时的预订在结果表中只有一行，即：预订后的第一个小时。这是因为预订的长度是在持续时间数据中捕获的。我们可以对数据进行一些后处理来达到我们的目的，但如果这在我们的 SQL 查询中是可能的，那就更容易了。

是否可以以某种方式修改此查询，以便根据持续时间（可以是 1、2、3、... 小时）将适当数量的行插入到结果表中，每行持续时间为 1。因此 3 - 小时预订从上午 9 点开始，结果表中会出现三行，一行在上午 9 点，一行在上午 10 点，一行在上午 11 点，每行持续时间为 1 小时。

而不是结果表中的以下行

Year, Month, Day, Hour, Duration, Court
2009,    08,  25,   09,        3,     1

因此，我们得到以下行，

Year, Month, Day, Hour, Duration, Court
2009,    08,  25,   09,        1,     1
2009,    08,  25,   10,        1,     1
2009,    08,  25,   11,        1,     1

：这将使将结果表映射到电子表格中更加容易。

更新2009-08-25：当然，正如前几个答案所示，它不一定是单个查询。一套就好了

更新2009-08-26：已经偏离轨道，还没有机会尝试建议的解决方案。希望尽快这样做，并将根据结果选择答案。

2009-08-27 更新：终于有机会尝试解决方案了。整数表和连接产生解决方案令人大开眼界。特别是使用交叉联接来创建这样的表。这可能是更简洁的 SQL 处理方式。

然而，最终我选择了 Aaron 涉及标志和简单算法的解决方案。我确实通过将他的算法包装在 while 循环中以不断迭代直到没有持续时间 > 来增强它。还剩 1 名。这是快速且容易实施的。它还强调我们确实有一些 10 小时的预订，所以我不需要在这里硬编码限制。

我应该注意到，我将 Jeff 的最大持续时间的想法融入到 while 循环计数器中，而不是我最初的想法，即对持续时间 > 的项目进行计数。 1.代码稍少。

Answer 1

编辑以更正缺失的小时计算

创建一个单列临时表，其中包含 n 行整数 n -（我假设最大预订时间为8小时）。

create table #t
(id int
,addHour int
)

insert #t
select 1,0
union all select 2,0
union all select 2,1
union all select 3,0
union all select 3,1
union all select 3,2
union all select 4,0
union all select 4,1
union all select 4,2
union all select 4,3
union all select 5,0
union all select 5,1
union all select 5,2
union all select 5,3
union all select 5,4
union all select 6,0
union all select 6,1
union all select 6,2
union all select 6,3
union all select 6,4
union all select 6,5
union all select 7,0
union all select 7,1
union all select 7,2
union all select 7,3
union all select 7,4
union all select 7,5
union all select 7,6
union all select 8,0
union all select 8,1
union all select 8,2
union all select 8,3
union all select 8,4
union all select 8,5
union all select 8,6
union all select 8,7

您可以使用以下查询验证临时表是否具有正确的行数：

select id, count(1)  
from #t
group by id
order by id

修改您的查询以包含临时表的联接：

INSERT INTO Results (year, month, day, hour, duration, court)
SELECT DATEPART (yy, b.StartDateTime),
       DATEPART (mm, b.StartDateTime),
       DATEPART (dd, b.StartDateTime),
       DATEPART (hh, b.StartDateTime) + addHour,
       1 AS Duration,
       a.Court 
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
INNER JOIN #t AS t
ON t.id = a.Duration

编辑 - 说明其工作原理

当联接表时， row 是在源表中满足连接条件的连接行的每个组合的输出中生成的。

我使用临时表将预订和活动的原始结果集“乘以”预订持续的小时数（通过加入持续时间）。仅当预订是在整数小时内进行时才有效。

如果您想更清楚地看到这一点，请向 #t 添加第二列，该列唯一标识每一行并将其包含在输出结果集中：

create table #t
(id int
,unique_id int identity
)

INSERT #t (id)
select 1
union all select 2
... etc

SELECT DATEPART (yy, b.StartDateTime),
       DATEPART (mm, b.StartDateTime),
       DATEPART (dd, b.StartDateTime),
       DATEPART (hh, b.StartDateTime) + addHour,
       1 AS Duration,
       a.Court,
       t.unique_id
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
INNER JOIN #t AS t
ON t.id = a.Duration

这应该澄清结果集中的每一行都是由 Bookings 的单个有效组合生成的，活动和#t。

Answer 2

如果您引入 整数，则对原始内容稍加修改就足够了 表（或 VIEW）用作系列生成器：

INSERT INTO Results (year, month, day, hour, duration, court)
SELECT DATEPART (yy, b.StartDateTime),
       DATEPART (mm, b.StartDateTime),
       DATEPART (dd, b.StartDateTime),
       DATEPART (hh, b.StartDateTime) + (a.Duration - i.iii - 1)
       1,
       a.Court
FROM Bookings b
INNER JOIN Activities a
  ON b.ActivityID = a.ID
INNER JOIN Integers999 i       -- N.B.: Integers999 (iii INT), all numbers 0 .. 999
  ON a.Duration > i.iii;       -- So, a true Duration of 1 appears once, of 2 twice ...

Answer 3

这不是小事。首先，您需要另一列“Flag”，其值为 0：

INSERT INTO Results (year, month, day, hour, duration, court, Flag)
SELECT DATEPART (yy, b.StartDateTime),
       DATEPART (mm, b.StartDateTime),
       DATEPART (dd, b.StartDateTime),
       DATEPART (hh, b.StartDateTime),
       a.Duration,
       a.Court,
       0
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID

您需要多次运行这些查询：

-- Copy all rows with duration > 1 and set the flag to 1
insert into results(year, month, day, hour, duration, court, Flag)
select year, month, day, hour+1, duration-1, court, 1
from result
where duration > 1
;
-- Set the duration of all copied rows to 1
update result
set duration = 1
where flag = 0 and duration > 1
;
-- Prepare the copies for the next round
update result
set flag = 0
where flag = 1

这将为每个 duration > 创建一个附加条目。 1..我的猜测是，你不能分配一个超过 8 小时的球场，所以你只需要运行这三个 8 次即可修复所有问题。

Answer 4

您可以考虑在表“结果”上放置一个 INSTEAD OF INSERT 触发器，该触发器将为每个超过一小时的预订插入多行。这确实增加了复杂性，但它可能是一种合理的方法，因为这听起来不像是大容量 OLTP 系统。

Answer 5

我还没有机会对此进行调试，但是类似这样的事情应该可以为您完成：

DECLARE @maxDuration    INTEGER
DECLARE @curDuration    INTEGER

SELECT @MaxDuration = SELECT MAX(Duration) FROM Activities
SET @curDuration = 1

WHILE @curDuration <= @MaxDuration
BEGIN
    INSERT INTO Results (year, month, day, hour, duration, court)
    SELECT DATEPART (yy, b.StartDateTime),
           DATEPART (mm, b.StartDateTime),
           DATEPART (dd, b.StartDateTime),
           DATEPART (hh, b.StartDateTime) + @curDuration - 1,
           a.Duration,
           a.Court
    FROM Bookings b
    INNER JOIN Activities a
    ON b.ActivityID = a.ID
    WHERE a.Duration <= @MaxDuration

    SET @curDuration = @curDuration + 1
END