sql - 在 2 个案例参数(日期范围和行数)之间循环 | SQL Server 2012

Question

我正在尝试了解如何正确地对该表执行该查询。输出应该只基于计数表循环，但会显示表行参数内的所有日期。

我为 APPLE、ORANGE 和 MANGO 分离了数据库，因为我需要模仿我的数据库结构。需要从不同的数据库调用数据，然后处理Date Range和Row Count的循环。

我从这个问题中得到了想法，并使用了一些代码来尝试复制它:

SQL how to convert row with date range to many rows with each date

这是预期的输出。

这是我要修复的查询

DECLARE @dbApple TABLE
                 (
                     FromDate VARCHAR(30) NOT NULL,
                     ToDate VARCHAR(30) NOT NULL,
                     Name VARCHAR(30) NOT NULL,
                     Count VARCHAR(30) NOT NULL
                 )

INSERT INTO @dbApple (FromDate, ToDate, Name, Count) 
VALUES ('2019-10-05', '2019-10-09', 'APPLE', '3');

DECLARE @dbOrange TABLE
                  (
                      FromDate VARCHAR(30) NOT NULL,
                      ToDate VARCHAR(30) NOT NULL,
                      Name VARCHAR(30) NOT NULL,
                      Count VARCHAR(30) NOT NULL
                  )

INSERT INTO @dbOrange (FromDate, ToDate, Name, Count) 
VALUES ('2019-10-10', '2019-10-14', 'ORANGE', '2'); 

DECLARE @dbMango TABLE
                 (
                      FromDate VARCHAR(30) NOT NULL,
                      ToDate VARCHAR(30) NOT NULL,
                      Name VARCHAR(30) NOT NULL,
                      Count VARCHAR(30) NOT NULL
                  )

INSERT INTO @dbMango (FromDate, ToDate, Name, Count) 
VALUES ('2019-10-15', '2019-10-19', 'MANGO', '4');  

(SELECT
     CONVERT(DATE, CONVERT(DATE, DATEADD(D, v.number, FromDate))) AS Date,
     DB.Name,
     CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY Count, FromDate, ToDate ORDER BY Count) = Count
           THEN Count
           ELSE NULL 
     END AS Count
 FROM 
     @dbApple DB
 JOIN 
     MASTER..spt_values v ON v.TYPE = 'P'
                          AND v.number BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

UNION 

(SELECT
     CONVERT(DATE, DATEADD(D, v.number, FromDate)) AS Date,
     DB.Name,
     CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY Count, FromDate, ToDate ORDER BY Count) = Count
           THEN Count
           ELSE NULL 
     END AS Count
 FROM 
     @dbOrange DB
 JOIN 
     MASTER..spt_values v ON v.TYPE = 'P'
                          AND v.number BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

UNION 

(SELECT
     CONVERT(DATE, DATEADD(D, v.number, FromDate)) AS Date,
     DB.Name,
     CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY Count, FromDate, ToDate ORDER BY Count) = Count 
           THEN Count
           ELSE NULL 
     END AS Count
 FROM 
     @dbMango DB
 JOIN 
     MASTER..spt_values v ON v.TYPE = 'P'
                          AND v.number BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

这是输出:

1. 尝试使用 CASE WITHIN CASE 但没有成功。

declare @dbApple TABLE(
    FromDate varchar(30) NOT NULL,
    ToDate varchar(30) NOT NULL,
    Name varchar(30) NOT NULL,
    Count varchar(30) NOT NULL
)

INSERT INTO @dbApple
(FromDate,ToDate,Name,Count) VALUES ('2019-10-05','2019-10-09','APPLE','3');

(SELECT
    CONVERT(date,CONVERT(date,DATEADD(D,VAL.NUMBER,FromDate))) AS Date,
    DB.Name,
    CASE WHEN CONVERT(date,CONVERT(date,DATEADD(D,VAL.NUMBER,FromDate))) BETWEEN 
    CONVERT(date,CONVERT(date,DATEADD(D,VAL.NUMBER,FromDate)))  AND 
    CONVERT(date,CONVERT(date,DATEADD(D,VAL.NUMBER,ToDate)))
THEN CASE WHEN ROW_NUMBER() 
    OVER(PARTITION BY Count,FromDate,ToDate
    ORDER BY Count) = Count
THEN Count
    ELSE '1' END
    ELSE NULL END AS Count
FROM 
    @dbApple DB
JOIN MASTER..SPT_VALUES VAL on VAL.TYPE='P'
    AND VAL.NUMBER BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

使用 CASE WITHIN CASE 输出。

Answer 1

如何递归地解决这个问题？

先把水果放在篮子里，然后去皮。

WITH BASKET AS 
(
   SELECT FromDate, ToDate, Name, Count
   FROM @dbApple
   UNION ALL
   SELECT FromDate, ToDate, Name, Count
   FROM @dbOrange
   UNION ALL
   SELECT FromDate, ToDate, Name, Count
   FROM @dbMango
),
PEELED AS
(
    SELECT 
     FromDate as [Date], 1 as Lvl,
     FromDate, ToDate, Name, Count
    FROM BASKET

    UNION ALL

    SELECT 
     DATEADD(day,1,[Date]), Lvl +1, 
     FromDate, ToDate, Name, Count
    FROM PEELED p
    WHERE [Date] < ToDate
)
SELECT [Date], [Name], 
CASE WHEN Lvl <= Count THEN 1 END AS [Count]
FROM PEELED
ORDER BY [Date];

rextester 上的测试 here