文章/答案/技术大牛

发布

问加上每一天的住宿日期
EN

Database Administration用户

提问于 2021-06-08 16:36:19

回答 2查看 70关注 0票数 0

假设我们有一家小旅店。人们在网上预订。我收到预订号，他们将到达的日期和停留的时间。我不仅想知道他们逗留的天数，还想知道他们的实际日历日期。例如，如果我的源表如下所示：

1000|5|6/5/2021
1001|3|6/7/2021

那么，我希望输出是：

1000|6/5/2021
1000|6/6/2021
1000|6/7/2021
1000|6/8/2021
1000|6/9/2021
1001|6/7/2021
1001|6/8/2021
1001|6/9/2021

我一直在使用下面的代码来生成日期，但是我很难将它加入到原始数据中。

DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate = '2021-11-01', @EndDate = '2021-12-01'; 
WITH ListDates(AllDates) AS
(   SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < @EndDate)
SELECT AllDates
FROM ListDates
GO

sql-server

t-sql

回答 2

Database Administration用户

回答已采纳

发布于 2021-06-08 17:06:53

你离它很近。(通常，有一个日历表并使用它，而不是递归查询。你可以在网上搜索一个例子)

我不知道您是收到了文本/字符串(用于数据源)还是它的表。在这个解决方案中，我使用了一个表(@tv_Source)

有一种方法：

DECLARE @StartDate DATE, @EndDate DATE
DECLARE @tv_Source TABLE
    (
        id int, noDays int,startDate date
    );
INSERT INTO @tv_Source(id,noDays,startDate)
VALUES(1000,5,'6/5/2021'),
     (1001,3,'6/7/2021');

SELECT @StartDate = '2021-06-01', @EndDate = '2021-07-01'; 
WITH ListDates(AllDates) AS
(   SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < @EndDate)

SELECT AllDates,s.id
FROM ListDates as l
 INNER JOIN @tv_Source as s
    ON l.AllDates >=s.startDate 
        AND l.AllDates< DATEADD(dAY,s.noDays,s.startDate)
ORDER BY s.id ASC,l.AllDates ASC
OPTION (MAXRECURSION 0)

产出：

AllDates   id
2021-06-05 1000
2021-06-06 1000
2021-06-07 1000
2021-06-08 1000
2021-06-09 1000
2021-06-07 1001
2021-06-08 1001
2021-06-09 1001

票数 4

Database Administration用户

发布于 2021-06-08 17:14:40

您可以执行以下操作(下面的所有代码都可以在这里上使用)：

CREATE TABLE booking
(
  bk_no     INTEGER NOT NULL PRIMARY KEY,
  bk_nights INTEGER NOT NULL CHECK (bk_nights > 0 AND bk_nights < 100), -- 100 arbitrary
  bk_sdt    DATE NOT NULL
);

填充表：

INSERT INTO booking
VALUES
(1000, 5, '2021-05-06'),
(1001, 3, '2021-05-06');

创造一张新的桌子来举办我们的预订之夜：

CREATE TABLE booking_nights
(
  nt_no    INTEGER NOT NULL,
  bk_no    INTEGER NOT NULL,
  bk_night DATE NOT NULL,
  
  PRIMARY KEY (bk_no, bk_night)
);

然后运行以下查询--这是一个RECURSIVE CTE：

WITH cte (n, bkno, bknt, bkdt) AS
(
  SELECT
    1,
    bk_no, 
    bk_nights,
    bk_sdt
  FROM booking
  UNION ALL
  SELECT
    n + 1,
    bkno,
    bknt,
    DATEADD(DAY, 1, bkdt)
  FROM cte
  WHERE n < bknt
        
)
INSERT INTO booking_nights
SELECT c.n, c.bkno, c.bkdt 
FROM cte c
ORDER BY c.bkno, c.bkdt;

然后：

SELECT * FROM booking_nights
ORDER BY bk_no, bk_night;

结果：

 nt_no  bk_no   bk_night
     1   1000   2021-05-06
     2   1000   2021-05-07
     3   1000   2021-05-08
     4   1000   2021-05-09
     5   1000   2021-05-10
     1   1001   2021-05-06
     2   1001   2021-05-07
     3   1001   2021-05-08
8 rows

表定义中关于CHECK约束部分的说明，即：

bk_nights INTEGER NOT NULL CHECK (bk_nights > 0 AND bk_nights < 100), -- 100 arbitrary

在主RCTE中使用此约束与n变量结合时，迭代的最大次数将为100次。在发生数据错误时，如果没有限制，就有进入无限循环的风险！
设置OPTION (MAXRECURSION 0);是危险的，因为刹车被停用(dbfiddle最终会给出一个Run failed错误)。另一种选择可能是将MAXRECURSION设置为100，并且在表定义中没有约束，但是没有刹车的“驱动”是不可取的。可以设置为32,767的最大值(有符号32位整数的最高可能值)。这是在我的这里的另一个答案中讨论的。

性能分析是可用的这里。

票数 1

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/293948

复制

相似问题

问加上每一天的住宿日期
EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问加上每一天的住宿日期EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问加上每一天的住宿日期
EN