SQL Server - 存储过程中的模拟记录

Apologies if this is a duplicate - I have searched for an answer, but what I found isn't exactly what I'm looking for.

如果这是重复的道歉 - 我已经找到了答案，但我找到的并不是我正在寻找的。

I have a very simple stored procedure that returns date based stock from an inventory table. It takes start date and end date params with a stockist id.

我有一个非常简单的存储过程，它从库存表中返回基于日期的库存。它需要一个带有stockist id的开始日期和结束日期参数。

The query is like the following...

查询如下...

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

What I need to ensure is that there is always a row returned for the date range requested - even if there is no record in the Database for that date and stockist. Mocked records will have an Amount of Zero.

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

The data is being fed through to an external system, so I don't need to insert the records unless said system increases the value from zero (I have no need for storing thousands of null records in the table).

数据被馈送到外部系统，因此我不需要插入记录，除非所述系统将值从零增加（我不需要在表中存储数千个空记录）。

If a subsequent call from the external system increases the amount, at this point I will insert the record to the table.

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

I know I could do this by creating a temp table variable and inserting non-existing records into the result set - I just wondered if I can do anything in the query to prevent having to use table variables.

我知道我可以通过创建临时表变量并将不存在的记录插入结果集来实现这一点 - 我只是想知道我是否可以在查询中执行任何操作以防止必须使用表变量。

Again, apologies if this has already been asked.

再次，如果已经被问过，请道歉。

I'm using SQL Server 2014.

我正在使用SQL Server 2014。

Thanks

谢谢

Example

例

Here's a sample of data from the Stock Table

这是Stock表中的数据样本

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

If I run my query for the dates 12/04/2017 - 14/04/2017 for Stockist 1, I get the following...

如果我对Stockist 1的日期12/04/2017 - 14/04/2017运行查询，我会得到以下信息......

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

As there is no record for 13/04/2017

由于没有2017年4月13日的记录

What I would like to return is...

我想回来的是......

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

Where my query has 'mocked' up a record for 13/04/2017

我的查询在2017年4月13日“嘲笑”了一个记录

2 个解决方案

#1

If you have a calendar table this becomes a very easy query.

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

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;

If you don't have a calendar table I would recommend that you create one, they are very useful. If you can't create one it is fairly easy to generate one on the fly:

如果你没有日历表我会建议你创建一个日历表，它们非常有用。如果你不能创建一个，那么动态生成一个很容易：

First just generate a series of numbers using cross joins and ROW_NUMBER()

首先使用交叉连接和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;

This will generate numbers from 0 - 9999, which should cover any date range you need.

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

Then you can use this number along with DATEADD to get the days in your range:

然后，您可以使用此数字和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;

Which gives you your dates.

哪个给你你的约会。

Then for the final step you just need to left join to your stocks table

然后在最后一步，你只需要加入你的股票表

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;

More about calendar tables, and generating series without loops can be found in a 3 part article:

有关日历表的更多信息，以及无循环的生成系列，请参阅3部分文章：

Generate a set or sequence without loops – part 1
生成没有循环的集合或序列 - 第1部分
Generate a set or sequence without loops – part 2
生成没有循环的集合或序列 - 第2部分
Generate a set or sequence without loops – part 3
生成没有循环的集合或序列 - 第3部分

#2

While accepted answer is perfectly fine, I'd just like to provide another way to create Dates on fly - using recursive CTE. Maybe debatable, but I'd say it's simpler.

虽然接受的答案非常好，但我想提供另一种方法来创建日期 - 使用递归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

#1