SQL查询以常量格式返回聚合，即使在缺少类别的情况下也是如此

Question

SQL Server是否可以用常量格式返回表？

假设我们有以下原始数据：

DATE       |  CATEGORY  |  VALUE
---------------------------------
01.01.2022 | Category 1 |   10
01.01.2022 | Category 1 |   20
01.01.2022 | Category 1 |   33
01.01.2022 | Category 3 |   15
03.01.2022 | Category 1 |   10
03.01.2022 | Category 2 |   20
03.01.2022 | Category 3 |   50
(...)

所需的输出是：

DATE       |  CATEGORY  |  VALUE
---------------------------------
01.01.2022 | Category 1 |    63
01.01.2022 | Category 2 |     0
01.01.2022 | Category 3 |    15
02.01.2022 | Category 1 |     0
02.01.2022 | Category 2 |     0
02.01.2022 | Category 3 |     0
03.01.2022 | Category 1 |    10
03.01.2022 | Category 2 |    20
03.01.2022 | Category 3 |    50
(...)

请注意，在所需的结果中，原始数据中缺少一个日期，并且当原始数据中给定日期的类别不存在时，VALUE的总和为0。

Answer 1

-- Contiguous dates table
DECLARE @dates TABLE(dt date) ;   
DECLARE @dateFrom date;
DECLARE @dateTo date;
select @dateFrom = (Select DateAdd(day, -1, Min(date)) from Agg);
select @dateTo = (Select Max(date) from Agg);
-- Query:

WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
INSERT INTO @dates 
SELECT @dateFrom
END
-- Category table 
DECLARE @categories TABLE(category nvarchar(20)) ;
insert into @categories values ('Category 1'),('Category 2'),('Category 3');
-- This cte helps in creating the constant output required 
with cte1 as (
select dt, category from 
@dates cross join @categories
) 
select cte1.dt as [Date], cte1.category, Sum(coalesce(yourTableName.value,0)) as Value
from cte1 left join yourTableName
on cte1.dt = yourTableName.[Date] and cte1.category = yourTableName.category
group by cte1.dt, cte1.category
order by cte1.dt, cte1.category

Answer 2

WITH  ctedate AS
(
SELECT d= v2.d * 10 + v1.d
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(d)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(d)
)
Select b.date, a.category, sum(coalesce(a.value,0)) from 
yourtablename a 
cross join
(SELECT DATEADD(DAY, ctedate.d, '2022-01-01') date
FROM ctedate
ORDER BY ctedate.d) b 
group by b.date, a.category

Answer 3

下面是一个使用cte:的解决方案的脚本

1. 计算最小日期和最大日期

2. 然后使用递归制作一个包含(最小日期(和(最大日期(之间所有日期的日历(cte1(

3. 获取所有类别的列表=>cte2

4. 在cte1和cte2之间进行交叉连接==>cte3

5. 在cte3和表数据(@mytable(之间进行左联接，将Null值替换为0

   声明@mytable为表(日期日期，类别varchar(50(，值int(

   插入@mytable值('01/2022'，"类别1"，10(，('01/2022'，"类别1"，20(，('01/2022'，"类别1"，33(，('01/2022'，'类别3'，15(，('01/03/2022’，"类别1"，10(，('01/03/2022’，‘类别2’，20(，('01/2022'，"类别3"，50(；

   声明@mindate为日期，@maxdate为日期

   从@mytable中选择@mindate=min(日期(，@maxdate=max(日期(；

   与cte1 as(select@mindate mydate union all select dateadd(day，1，mydate(from cte1 where dateadd(date，1，my date(<=@maxdate(，cte2为(从@mytable中选择distinct(类别((，cte3为(从cte1中选择mydate，类别交叉加入cte2(，cte4为(选择mydate date，cte3.category，是cte3左侧外部联接到cte3上的mytable t的全部(值，0(值。mydate=t.date和cte3.ccategory=t.category(从cte4 中选择*