问题描述
我在数据库中有一个包含租户数量的表格，每个租户列出了他们每个日期的销售记录.在某些情况下，租户在特定日期没有销售，因此没有销售的日期在表格中没有打破正确日期顺序的记录.请参阅下面的示例表:
I have a table in database with numbers of tenants, each tenant lists a record of their sales per date. There are instance where in a tenant has NO SALES in particular date/s, therefore the date with no sales has NO RECORD in the table breaking a proper date sequence. Please see the sample table for illustration below:
我在 SQL 中使用了这个选择查询来显示上面的输出
I used this select query in SQL to display the output above
select tenant, date, sales
from tblSales
where date between '01/01/2015' and '01/05/2014'
我需要什么作为正确的输出:根据 where 子句中选定的日期范围显示完整日期，当租户在特定日期没有记录时，查询应在该特定租户中添加日期记录并添加销售列中的空值，如下图所示:
What I need as a correct output: display complete date based on the selected date range on the where clause, when tenant has no record in a particular date, the query should add a record of date in that particular tenant and just add null value in the sales column like in this image:
- 作为我最初的解决方案，我想创建一个临时表，根据所选日期范围插入一系列日期，并使用它与实际表左连接.
下面是我的开始:
@dateFrom datetime = '02/01/2015',
@dateTo date = '02/05/2015'
declare @MaxNumDays int
declare @Counter int
set @Counter = 0
set @MaxNumDays = DATEDIFF(day, @dateFrom , @dateto) + 1
create table #DSRTdate (
Date datetime
)
WHILE @Counter < @MaxNumDays
BEGIN
insert into #DSRTdate (Date) values (DATEADD(day,@Counter,@dateFrom ))
SET @Counter += 1
END
我使用上述代码从使用选择中获取并插入临时表中的序列数据，在上述情况下，它插入 02/01/2015, 02/02/2015, 02/03/2015, 02/04/2015 和 02/05/2015
I used the above codes to get and insert in a temporary table the sequence data from the use selection, in the above case, it inserts 02/01/2015, 02/02/2015, 02/03/2015, 02/04/2015, AND 02/05/2015
select tenantcode, date, sales
into #DSRT2
from DAILYMOD
where (date between @dateFrom and @dateTo)
select *
from #dsrtdate a
left join #DSRT2 b on a.date = b.date
order by b.tenantcode, a.date
然后我使用左连接来显示缺少的日期，但这只会导致一个租户，并且它也使租户名称为空.像这样:
Then I used left join to display the missing dates but this results only to ONE TENANT only and it makes also the tenantname null. Like this:
任何建议都将受到高度赞赏.
Any suggestions would be highly appreciated.
推荐答案
你可以使用 理货表.
You could do this using a Tally Table.
基本上，您使用 Tally Table 生成从 @startDate 到 @endDate 和 CROSS JOIN 将其以DISTINCT Item 生成所有Date-Item 组合.然后，结果将被 LEFT-JOINed 到 tblSales 以实现所需的输出.
Basically, you use the Tally Table to generate sequence of dates from @startDate to @endDate and CROSS JOIN it to DISTINCT Item to generate all Date-Item combination. Then, the result will be LEFT-JOINed to tblSales to achieve the desired output.
SQL 小提琴
DECLARE
@startDate DATE = '20140101',
@endDate DATE = '20140105';
WITH E1(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM E4
)
,CteAllDates(Item, dt) AS(
SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
FROM Tally
CROSS JOIN(
SELECT DISTINCT Item
FROM tblSales
WHERE [Date] BETWEEN @startDate AND @endDate
) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
ON ts.Item = d.Item
AND ts.Date = d.dt
WHERE
ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt
<小时>
这是另一种选择.代替级联的 CTE，使用 sys.columns 来生成 Tally Table.:
Here is an alternative. Instead of the cascading CTEs, use sys.columns to generate the Tally Table.:
DECLARE
@startDate DATE = '20140101',
@endDate DATE = '20140105';
WITH Tally(N) AS(
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.columns a, sys.columns b
)
,CteAllDates(Item, dt) AS(
SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
FROM Tally
CROSS JOIN(
SELECT DISTINCT Item
FROM tblSales
WHERE [Date] BETWEEN @startDate AND @endDate
) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
ON ts.Item = d.Item
AND ts.Date = d.dt
WHERE
ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt
<小时>
结果
| Item | dt | Sales |
|---------|------------|--------|
| tenant1 | 2014-01-01 | 100 |
| tenant1 | 2014-01-02 | 100 |
| tenant1 | 2014-01-03 | 100 |
| tenant1 | 2014-01-04 | NULL |
| tenant1 | 2014-01-05 | 100 |
| tenant2 | 2014-01-01 | 100 |
| tenant2 | 2014-01-02 | NULL |
| tenant2 | 2014-01-03 | NULL |
| tenant2 | 2014-01-04 | 100 |
| tenant2 | 2014-01-05 | NULL |
| tenant3 | 2014-01-01 | 100 |
| tenant3 | 2014-01-02 | NULL |
| tenant3 | 2014-01-03 | 100 |
| tenant3 | 2014-01-04 | NULL |
| tenant3 | 2014-01-05 | 100 |
这篇关于使用 Tally 表在 SQL 中按日期排序填充缺失的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！