将表与日期列表连接 - 包括对 NULL 的引用

Question

我有更多的大脑冻结时刻。我相信这将是一个容易的。

我有两张桌子。一个是按周列出的零件使用情况列表。这称为 TransactionsPerWeek，如下所示：

ItemPK       xWeek     xYear      TotalQty
1234         2         2019        65
1234         4         2019        15
1234         5         2019        50

我还有一个包含周数和年份的 DateList 表

xWeek       xYear
1          2019
2          2019
3          2019

等。

当我在一周和一年中将两者结合在一起时，我得到

ItemPK     xWeek    xYear    TotalQty
NULL        1       2019       0
1234        2       2019      65
NULL        3       2019       0
1234        4       2019      15
1234        5       2019      50

我需要的是每行都有 ItemPK，即使 TotalQty 是 0。所以实际上，我需要：

ItemPK     xWeek    xYear    TotalQty
1234        1       2019       0
1234        2       2019      65
1234        3       2019       0
1234        4       2019      15
1234        5       2019      50

这是我的代码...

SELECT  itemfk,
dates.year,
dates.week,
isnull(transactionsperweek.TotalQty,0) as TotalQty
from    (
SELECT  iit.ItemFK,
year(iit.transactiondate) xYear,
datepart(wk,iit.transactiondate) xWeek,
abs(sum(iit.quantity)) TotalQty
from    iteminventorytransaction iit
INNER JOIN ItemInventoryTransactionType iitt on ItemInventoryTransactionTypePK = iit.ItemInventoryTransactionTypeFK
where   iit.itemfk = 5311
and iit.ItemInventoryTransactionTypeFK in (10,8)
and iit.TransactionDate BETWEEN
-- 1 year up to the sunday of last week 
DateAdd(wk,-51,DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE()))
AND DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE())
AND Quantity < 0
group by iit.itemfk,
year(iit.transactiondate),
datepart(wk,iit.transactiondate)
) transactionsPerWeek
RIGHT JOIN  (
select  year,
week
from    DatesList
where   date > DateAdd(wk,-51,DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE()))
AND date < DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE())
group by year,
week
) Dates ON dates.week = transactionsPerWeek.xWeek
AND dates.year = transactionsPerWeek.xYear
where   week not in (52,53)

希望这足够清楚。提前谢谢。

Answer 1

您可以使用递归 cte ：

with cte as (
select 1 as id, max(xWeek) as maxwk
from TransactionsPerWeek 
union all
select id + 1, maxwk
from cte c
where c.id < maxwk
)
select coalesce(wk.ItemPK, wk1.ItemPK) as ItemPK, c.id as xWeek, wk.xYear, wk.TotalQty
from cte c left join
TransactionsPerWeek wk
on wk.xWeek = c.id outer apply
( select top (1) wk1.ItemPK 
from TransactionsPerWeek wk1
where wk1.xWeek >= c.id and wk1.xWeek is not null
order by wk1.xWeek
) wk1;

Answer 2

好的，所以我按照@larnu的建议做了，并将该项目与日期交叉连接，然后将其左边连接到每周事务表，它起作用了。谢谢。

这是我现在的代码;

SELECT itempk, week, year
, ISNULL(transactionsPerWeek.TotalQty,0) as TotalQty
from item
CROSS JOIN
(
select year, week from DatesList where date > 
DateAdd(wk,-51,DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE()))
AND date <
DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE())
group by year, week
) dates
LEFT JOIN 
(
SELECT iit.ItemFK, year(iit.transactiondate) xYear, datepart(wk,iit.transactiondate) xWeek, abs(sum(iit.quantity)) TotalQty from iteminventorytransaction iit
INNER JOIN ItemInventoryTransactionType iitt on ItemInventoryTransactionTypePK = iit.ItemInventoryTransactionTypeFK
where iit.itemfk = 5311 and iit.ItemInventoryTransactionTypeFK in (10,8)
and iit.TransactionDate BETWEEN
-- 1 year up to the sunday of last week 
DateAdd(wk,-51,DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE()))
AND
DATEADD(day,-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,GETDATE())
AND Quantity < 0
group by iit.itemfk, year(iit.transactiondate), datepart(wk,iit.transactiondate)
) transactionsPerWeek
ON itempk = transactionsperweek.ItemFK and transactionsPerWeek.xYear = dates.year and transactionsPerWeek.xWeek = dates.week
where itempk = 5311

Answer 3

使用cross join生成行，使用left join来引入已有的结果。

您的问题明确指出您有两个表。 因此，我不知道您的SQL代码在做什么，因为它没有引用这些表。 因此，根据描述：

select i.ItemPK, d.xWeek, d.xYear,
coalesce(TotalQty, 0) as TotalQty
from (select distinct itemPK from TransactionsPerWeek
) i cross join
DateList d left join
TransactionsPerWeek t
on t.itemPK = i.itemPK and
t.xWeek = d.xWeek and
t.xYear = d.xYear;

当然，如果"表"真的是子查询，那么我建议使用 CTE 并且仍然使用这种基本的查询结构。