Server从存储过程复制记录

Question

抱歉，如果这是重复的--我已经在寻找答案，但我发现的并不是我想要的。

我有一个非常简单的存储过程，从库存表返回基于日期的库存。它采用开始日期和end date params与股票id。

查询如下..。

select  
    S.Date, 
    S.Amount 
from Stock S
where 
    S.StockistID = @pi_stockistId and
    S.Date >= @pi_startDate and
    S.Date <= @pi_endDate

我需要确保的是，对于请求的日期范围，始终有一行返回--即使数据库中没有该日期的记录和库存列表。模拟记录的数量将为零。

数据被传送到外部系统，所以我不需要插入记录，除非上述系统将值从零增加(我不需要在表中存储数千条空记录)。

如果来自外部系统的后续调用增加了数量，此时我将将记录插入到表中。

我知道我可以通过创建一个临时表变量并将不存在的记录插入结果集中来实现这一点--我只是想知道我是否可以在查询中做任何事情来避免不得不使用表变量。

再一次，如果这已经被问到了，那就道歉。

我正在使用Server 2014。

谢谢

示例

以下是股票表中的数据样本

------------------------------------
| Date       | StockistId | Amount |
------------------------------------
| 12/04/2017 | 1          | 10     |
| 14/04/2017 | 1          | 20     |
------------------------------------

如果我运行我的查询日期12/04/2017 - 14/04/2017的股票1，我得到以下.

-----------------------
| Date       | Amount |
-----------------------
| 12/04/2017 | 10     |
| 14/04/2017 | 20     |
-----------------------

因为2017年4月13日没有记录

我想回来的是..。

-----------------------
| Date       | Amount |
-----------------------
| 12/04/2017 | 10     |
| 13/04/2017 | 0      |
| 14/04/2017 | 20     |
-----------------------

我的查询“模拟”了2017年4月13日的记录

Answer 1

如果您有一个日历表，这将成为一个非常简单的查询。

SELECT  c.Date,
        Amount = ISNULL(s.Amount, 0)
FROM    dbo.Calendar AS c
        LEFT JOIN Stock AS s
            ON s.Date = c.Date
            AND s.StockistID = @pi_stockistId
WHERE   c.Date >= @pi_startDate
AND     c.Date <= @pi_endDate;

如果您没有日历表，我建议您创建一个日历表，它们非常有用。如果您不能创建一个，那么动态生成一个是相当容易的：

首先，使用交叉连接和ROW_NUMBER()生成一系列数字

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT  ROW_NUMBER() OVER(ORDER BY N) - 1
FROM    N3;

这将生成0-9999的数字，这应该涵盖您需要的任何日期范围。

然后，您可以与DATEADD一起使用这个数字来获取范围内的天数：

DECLARE @pi_startDate DATE = '20170101',
        @pi_endDate DATE = '20170301';

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) - 1 FROM N3)

SELECT  TOP (DATEDIFF(DAY, @pi_startDate, @pi_endDate) + 1)
        Date = DATEADD(DAY, N, @pi_startDate)
FROM    Numbers;

这给了你约会的机会。

那么，在最后一步中，只需将联接留在您的股票表中即可。

DECLARE @pi_startDate DATE = '20170101',
        @pi_endDate DATE = '20170301';

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Calendar (Date) AS 
(   SELECT TOP (DATEDIFF(DAY, @pi_startDate, @pi_endDate) + 1)
            DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @pi_startDate)
    FROM    N2
)
SELECT  c.Date,
        Amount = ISNULL(s.Amount, 0)
FROM    Calendar AS c
        LEFT JOIN Stock AS s
            ON s.Date = c.Date
            AND s.StockistID = @pi_stockistId;

关于日历表的更多信息，以及生成不带循环的系列，可以在一篇3部分的文章中找到：

• 生成不带循环的集合或序列.第1部分
• 生成不带循环的集合或序列.第2部分
• 生成不带循环的集合或序列.第3部分

Answer 2

虽然接受的答案很好，但我只想提供另一种即时创建日期的方法--使用递归的CTE。也许有争议，但我认为这更简单。

DECLARE @startDate DATE = '20170401';
DECLARE @endDate DATE = '20170410';

WITH CTE_Dates AS 
(
    SELECT @StartDate AS Dt
    UNION ALL
    SELECT DATEADD(DAY,1,Dt)
    FROM CTE_Dates 
    WHERE Dt < @endDate
)
SELECT * 
FROM CTE_Dates
--LEFT JOIN to your data here
OPTION (MAXRECURSION 0); -- needed if range is more than 100 days