sql - 我可以通过 VIEW 中的 WHERE 子句设置递归 CTE 吗?
问题描述
我想让这个查询成为一个视图,它可以根据想要的任何时间范围生成递归日期 CTE。现在,出于本示例的目的,我已将开始日期固定为 2019 年 12 月 30 日,将递归的结束日期固定为 2020 年 1 月 4 日。我知道我没有在视图中设置变量的选项,但是,我想知道是否有办法间接地做到这一点。有什么方法可以使开始日期和 gen gap 结束日期取决于 where 子句?
这是查询
WITH Dates AS (
SELECT [GenGapDate] = CONVERT(DATETIME,'12/30/2019') -- // this is the starting date
UNION ALL
SELECT [GenGapDate] = DATEADD(DAY, 1, [GenGapDate])
FROM Dates
WHERE GenGapDate < '1/4/2020'
),
StoreXJoinDate as (
select d.[GenGapDate],
s.Store,
s.OpenDate,
s.CloseDate
from Dates as d
cross join #Stores s
),
POSQtySum as (
select
p.SaleDate,
p.Store,
sum(p.Qty) as TotQty
from #POS p
group by p.SaleDate, p.Store
),
StoreQty as (
select distinct
x.GenGapDate,
x.Store,
p.Store as PosStore,
x.OpenDate,
x.CloseDate,
isnull(p.TotQty,0) as TotQty
from StoreXJoinDate x
full outer join POSQtySum p on p.Store = x.Store
and p.SaleDate = x.GenGapDate
),
BaseResultSet as (
select GenGapDate,
Store,
OpenDate,
CloseDate,
TotQty,
1 as StoreIsMissing
from StoreQty
where TotQty = 0
and (
CloseDate is null
or (CloseDate >= GenGapDate)
)
and OpenDate is not null
and GenGapDate >= OpenDate
union
select GenGapDate,
Store,
OpenDate,
CloseDate,
TotQty,
NULL as StoreIsMissing
from StoreQty
where TotQty > 0
)
select GenGapDate,
Store,
OpenDate,
CloseDate,
TotQty,
StoreIsMissing
from BaseResultSet
order by GenGapDate desc, Store asc;
这是生成用于查询的数据的构建脚本。
create table #Stores -- drop table #Stores
(Store int,
OpenDate date,
CloseDate date
);
create table #ProdCat -- drop table #ProdCat
(ProdCatId int,
ProdCatName varchar(10)
);
create table #POS -- drop table #POS
(SaleDate date,
Store int,
ProdCatId int,
Qty int
);
-- Store inserts
insert into #Stores
(Store,OpenDate,CloseDate)
values
(123,'2019-12-31',NULL);
insert into #Stores
(Store,OpenDate,CloseDate)
values
(124,'1995-01-01',NULL);
insert into #Stores
(Store,OpenDate,CloseDate)
values
(125,'2000-01-01','2020-01-03');
-- ProdCat inserts
insert into #ProdCat
(ProdCatId,ProdCatName)
values
(1,'Produce');
insert into #ProdCat
(ProdCatId,ProdCatName)
values
(2,'Diary');
-- POS inserts
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2019-12-30',1,124,420);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2019-12-30',2,124,180);
-- --
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',1,123,10);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',2,123,10);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',1,124,500);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',2,124,200);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',1,125,50);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',2,125,0);
-- --
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',1,123,12);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',2,123,15);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',1,124,510);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',2,124,195);
-- --
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',1,123,6);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',2,123,10);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',1,124,610);
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',2,124,220);
解决方案
根据你想如何使用它,你可以把整个东西变成一个表值函数。
create function MissingStores (@startDate date, @endDate date)
returns table
as
return
(
WITH Dates AS (
SELECT [GenGapDate] = @startDate -- // this is the starting date
UNION ALL
SELECT [GenGapDate] = DATEADD(DAY, 1, [GenGapDate])
FROM Dates
WHERE GenGapDate < @endDate
),
StoreXJoinDate as (
... <The rest of your query....>
);
这实际上是 SQL Server 对参数化视图的实现。从这里开始,您将函数视为表格,但带有参数。请注意,在ORDER BY
函数之外。函数不支持ORDER BY
子句。
select *
from MissingStores ('20191230', '20200104')
order by GenGapDate desc, Store asc;
结果(请原谅 Rextester 的日期格式):
| | GenGapDate | Store | OpenDate | CloseDate | TotQty | StoreIsMissing |
+----+---------------------+-------+---------------------+---------------------+--------+----------------+
| 1 | 04.01.2020 00:00:00 | 123 | 31.12.2019 00:00:00 | NULL | 16 | NULL |
| 2 | 04.01.2020 00:00:00 | 124 | 01.01.1995 00:00:00 | NULL | 830 | NULL |
| 3 | 03.01.2020 00:00:00 | 123 | 31.12.2019 00:00:00 | NULL | 27 | NULL |
| 4 | 03.01.2020 00:00:00 | 124 | 01.01.1995 00:00:00 | NULL | 705 | NULL |
| 5 | 03.01.2020 00:00:00 | 125 | 01.01.2000 00:00:00 | 03.01.2020 00:00:00 | 0 | 1 |
| 6 | 02.01.2020 00:00:00 | 123 | 31.12.2019 00:00:00 | NULL | 20 | NULL |
| 7 | 02.01.2020 00:00:00 | 124 | 01.01.1995 00:00:00 | NULL | 700 | NULL |
| 8 | 02.01.2020 00:00:00 | 125 | 01.01.2000 00:00:00 | 03.01.2020 00:00:00 | 50 | NULL |
| 9 | 01.01.2020 00:00:00 | 123 | 31.12.2019 00:00:00 | NULL | 0 | 1 |
| 10 | 01.01.2020 00:00:00 | 124 | 01.01.1995 00:00:00 | NULL | 0 | 1 |
| 11 | 01.01.2020 00:00:00 | 125 | 01.01.2000 00:00:00 | 03.01.2020 00:00:00 | 0 | 1 |
| 12 | 31.12.2019 00:00:00 | 123 | 31.12.2019 00:00:00 | NULL | 0 | 1 |
| 13 | 31.12.2019 00:00:00 | 124 | 01.01.1995 00:00:00 | NULL | 0 | 1 |
| 14 | 31.12.2019 00:00:00 | 125 | 01.01.2000 00:00:00 | 03.01.2020 00:00:00 | 0 | 1 |
| 15 | 30.12.2019 00:00:00 | 124 | 01.01.1995 00:00:00 | NULL | 600 | NULL |
| 16 | 30.12.2019 00:00:00 | 125 | 01.01.2000 00:00:00 | 03.01.2020 00:00:00 | 0 | 1 |
+----+---------------------+-------+---------------------+---------------------+--------+----------------+
推荐阅读
- sql - SQL Server 查询与按字段错误分组
- spring-boot - Springboot 控制器无法加载 Thymeleaf html 页面
- php - 疯狂的 LDAP (AD) 密码更新问题 (php)
- android-studio - 我可以在房间数据库中自动增加 id,但是当刷新数据库时它显示双倍
- javascript - vue.js 应用程序中的未定义方法不返回数据
- azure - 使用 Azure Cli 删除 Azure 文件共享中的旧文件
- python - 熊猫每周时间表,包括假期
- node.js - 我需要在 NPM package.json 和主 index.html 中添加 Babel 和 React 还是只在一个地方就足够了?
- php - 使用 Str::random (或原生 php 方法)的两个标记重合的情况
- reactjs - 如何在 React Native 中实现类似 Android 片段的功能