34,575
社区成员
发帖
与我相关
我的任务
分享
;WITH A(Batch,F,T)AS(
SELECT 'A','0001','0015' UNION
SELECT 'A','0021','0033' UNION
SELECT 'B','0001','0055'
),B(o_Batch,o_no,n_Batch,n_No)AS(
SELECT 'A','0008','A','0009' UNION
SELECT 'A','0010','A','0011' UNION
SELECT 'A','0025','A','0028' UNION
SELECT 'B','0025','B','0044' UNION
SELECT 'B','0027','B','0054'
)
SELECT a.*,t1-f1+1 AS subTotal,b.*,COUNT(0)OVER(PARTITION BY a.Batch,a.F,a.T) ,t1-f1+1-COUNT(0)OVER(PARTITION BY a.Batch,a.F,a.T)
FROM a
CROSS APPLY(VALUES(CONVERT(INT,T),CONVERT(INT,F))) c(t1,f1)
LEFT JOIN B ON a.Batch=b.o_Batch AND CONVERT(INT,b.o_no) BETWEEN c.f1 AND c.t1
Batch F T subTotal o_Batch o_no n_Batch n_No
----- ---- ---- ----------- ------- ---- ------- ---- ----------- -----------
A 0001 0015 15 A 0008 A 0009 2 13
A 0001 0015 15 A 0010 A 0011 2 13
A 0021 0033 13 A 0025 A 0028 1 12
B 0001 0055 55 B 0025 B 0044 2 53
B 0001 0055 55 B 0027 B 0054 2 53
with cte as (
select a.批次,a.起始,a.终止,cast(a.终止 as int)-cast(a.起始 as int) as 小计,counr(b. 新号码 ) as 关系小计
from a left join b
on b.原批次 = a.批次 and b.原号码 between a. 起始 and a.终止
group by a.批次,a.起始,a.终止
)
select a.*
,b.*
,a.小计 - a.关系小计 as 正常小计
from cte as aleft join b
on b.原批次 = a.批次 and b.原号码 between a. 起始 and a.终止