在cte中使用带有多个变量的交叉联接

Question

我有一些订单号，想检查是否有任何数字被跳过。我将使用左表方法，即：

select * from 
#CreatedCrossTable (which has all possibilities)
Left Join #MainTableWhichHaveRealSerialNo mt
where
mt is null

订单无结构为：；代码类型.年.月.序列号"。例如："；DP 21.07.001"DP 21.07.002"；。。或者不是DP；FB.21.07.001"FB.21.07.002"；等

我想创建一个交叉表模式来确定跳过的SerialNo值(CreatedCrossTable以上(：

(序列号每月重置(

CodeType | Year | Month | SerialNo
DP         21       1        1
DP         21       1        2
DP         21       1        3
DP         21       1        4
...
(All SerialNos must increase max serial number of the original table's SerialNo (MainTableWhichHaveRealSerialNo) Also codeType,year and month values must match)
DP         21       2        1
DP         21       2        2
...
FB         21       1        1
FB         21       1        2
...
FB         21       1        1
FB         21       1        2
FB         21       1        3
...

每个代码和月份的序列号都有不同的最大编号用于创建CrossTable。我已经写了代码：

;WITH cteSerialNo AS
(
SELECT 1 AS ORDERNO
UNION ALL
SELECT (ORDERNO+1) AS ORDERNO FROM cteSerialNo WHERE ORDERNO < MAX_ORDER_NO
)
,cteMonthYear AS
(
SELECT CAST('2021.01.01' AS DATE) AS Dt

UNION ALL
SELECT DATEADD(MONTH , 1, Dt) AS Dt
FROM cteMonthYear
WHERE DATEADD (MONTH, 1, Dt) < GETDATE()
)
SELECT
*
FROM
(
SELECT
CODES.CODETYPE,
YEAR(Dts.Dt) AS 'YEAR', 
MONTH(Dts.Dt) AS 'MONTH'
FROM
##KK_TBL_CODETYPES AS CODES
CROSS JOIN cteMonthYear AS Dts
) AS CROSSTABLE
CROSS JOIN cteSerialNo AS cSN

如何在此代码中为每个变量输入(MAX_ORDER_NO(？

Answer 1

假设最大SerialNo值基于SerialNo列中的现有值，您只需要找到该SerialNo值之前的所有可能组合，然后删除源数据中匹配的组合：

-- Define test data
declare @t table(CodeType varchar(2),[Year] int,[Month] int,SerialNo int);
insert into @t values
('DP',21,1,1)
,('DP',21,1,2)
,('DP',21,1,3)
--,('DP',21,1,4) -- Missing so should be in Output
,('DP',21,1,5)
,('DP',21,2,1)
,('DP',21,2,2)
,('FB',21,1,1)
,('FB',21,1,2)
,('FB',21,2,1)
,('FB',21,2,2)
--,('FB',21,2,3) -- Missing so should be in Output
,('FB',21,2,4)
;
with m as  -- Get Max SerialNo for each grouping
(
select CodeType
,[Year]
,[Month]
,max(SerialNo) as MaxSerialNo
from @t
group by CodeType
,[Year]
,[Month]
)
,t as  -- Create a table with 10 rows in
(
select t
from(values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as t(t)
)
,n as  -- Self join those 10 rows 5 times to generate a possible 10*10*10*10*10 = 100,000 incrementing numbers using row_number
(
select top(select max(MaxSerialNo) from m) row_number() over (order by (select null)) as n
from t,t t2,t t3,t t4,t t5
)
-- Join from the numbers table to the source data to generate all possible SerialNo values up to the Max
select m.CodeType
,m.[Year]
,m.[Month]
,n.n as SerialNo
from n
left join m
on n.n <= m.MaxSerialNo
except -- Then exclude any that are in the source data
select CodeType
,[Year]
,[Month]
,SerialNo
from @t
order by CodeType
,[Year]
,[Month]
,SerialNo

输出

序列号2

代码类型	年	月
DP	21		4
FB	21	3