代码之家 › 专栏 › 技术社区 › dave

基于所选值的单个或多个插入

sql-server-2000 tsql sql-server sql

dave · 技术社区 · 17 年前

我们正在将网球场的预订信息从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。因此,从上午9点开始的3小时预订将在结果表中显示三行,一行在上午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-27:终于有机会尝试解决方案了。整数表和求解的连接令人大开眼界。特别是使用交叉连接来创建这样的表。这可能是更干净的SQL做事方式。

然而,最后,我还是选择了Aaron的解决方案,包括旗帜和简单的算法。我确实通过将他的算法包裹在while循环中来增强它,以不断迭代,直到没有持续时间>剩下1个。这是快速且易于实施的。它还强调,我们确实有大约10小时的预订,所以我不需要在这里硬编码限制。

5 回复 | 直到 17 年前

Aaron Digulla 17 年前

使用以下命令创建单列临时表 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

编辑 -对其工作原理的澄清

如果你想更清楚地看到这一点,请在#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

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

Ed Harper 17 年前

如果你引入一个 integers 表格(或 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 ...

pilcrow 17 年前

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次就可以解决所有问题。

Steve Kass 17 年前

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

Jeff Hornby 17 年前

我还没有机会调试这个,但这样的东西应该能帮你:

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