;WITH CTEA AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY 合同号 ORDER BY 制单日期)RN FROM A ) ,CTEB AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY 合同号 ORDER BY 制单日期)RN FROM B ) ,CTE AS( SELECT T1.合同号,T1.款号,T1.颜色 ,CASE WHEN T1.包装数>T2.走货数 THEN T2.走货数 ELSE T1.包装数 END 走货数 ,T1.[标准总用时(只)] ,T1.RN CurrentRN1 ,T2.RN CurrentRN2 ,CASE WHEN T1.包装数>T2.走货数 THEN 2 WHEN T1.包装数<T2.走货数 THEN 1 ELSE 0 END NextType ,ABS(T1.包装数-T2.走货数) NextNum FROM CTEA T1 JOIN CTEB T2 ON T1.RN=1 AND T2.RN=1 AND T1.合同号=T2.合同号 UNION ALL SELECT T2.合同号,T2.款号,T2.颜色 ,CASE WHEN T1.包装数>T2.NextNum THEN T2.NextNum ELSE T1.包装数 END ,T2.[标准总用时(只)] ,T1.RN ,T2.CurrentRN2 ,CASE WHEN T1.包装数>T2.NextNum THEN 2 WHEN T1.包装数<T2.NextNum THEN 1 ELSE 0 END ,ABS(T1.包装数-T2.NextNum) FROM CTEA T1 JOIN CTE T2 ON T2.NextType=1 AND T1.RN=T2.CurrentRN1+1 AND T1.合同号=T2.合同号 UNION ALL SELECT T1.合同号,T1.款号,T1.颜色 ,CASE WHEN T1.NextNum>T2.走货数 THEN T1.NextNum ELSE T2.走货数 END ,T1.[标准总用时(只)] ,T1.CurrentRN1 ,T2.RN ,CASE WHEN T1.NextNum>T2.走货数 THEN 2 WHEN T1.NextNum<T2.走货数 THEN 1 ELSE 0 END ,ABS(T1.NextNum-T2.走货数) FROM CTE T1 JOIN CTEB T2 ON T1.NextType=2 AND T2.RN=T1.CurrentRN2+1 AND T1.合同号=T2.合同号 UNION ALL SELECT T1.合同号,T1.款号,T1.颜色 ,CASE WHEN T1.包装数>T2.走货数 THEN T2.走货数 ELSE T1.包装数 END ,T1.[标准总用时(只)] ,T1.RN ,T2.RN ,CASE WHEN T1.包装数>T2.走货数 THEN 2 WHEN T1.包装数<T2.走货数 THEN 1 ELSE 0 END ,ABS(T1.包装数-T2.走货数) FROM CTE T0 JOIN CTEA T1 ON T0.CurrentRN1+1=T1.RN AND T0.合同号=T1.合同号 AND T0.NextType=0 JOIN CTEB T2 ON T0.CurrentRN2+1=T2.RN AND T1.合同号=T2.合同号 ) SELECT 合同号,款号,颜色,走货数,[标准总用时(只)] FROM CTESQL2005+有效
问下什么版本,版本高的话可以考虑使用分析函数 sum()over(partition by order by )
SELECT *,ROW_NUMBER()OVER(PARTITION BY 合同号 ORDER BY 制单日期)RN
FROM A
)
,CTEB AS(
SELECT *,ROW_NUMBER()OVER(PARTITION BY 合同号 ORDER BY 制单日期)RN
FROM B
)
,CTE AS(
SELECT T1.合同号,T1.款号,T1.颜色
,CASE WHEN T1.包装数>T2.走货数 THEN T2.走货数 ELSE T1.包装数 END 走货数
,T1.[标准总用时(只)]
,T1.RN CurrentRN1
,T2.RN CurrentRN2
,CASE WHEN T1.包装数>T2.走货数 THEN 2
WHEN T1.包装数<T2.走货数 THEN 1
ELSE 0 END NextType
,ABS(T1.包装数-T2.走货数) NextNum
FROM CTEA T1
JOIN CTEB T2 ON T1.RN=1 AND T2.RN=1 AND T1.合同号=T2.合同号
UNION ALL
SELECT T2.合同号,T2.款号,T2.颜色
,CASE WHEN T1.包装数>T2.NextNum THEN T2.NextNum ELSE T1.包装数 END
,T2.[标准总用时(只)]
,T1.RN
,T2.CurrentRN2
,CASE WHEN T1.包装数>T2.NextNum THEN 2
WHEN T1.包装数<T2.NextNum THEN 1
ELSE 0 END
,ABS(T1.包装数-T2.NextNum)
FROM CTEA T1
JOIN CTE T2 ON T2.NextType=1 AND T1.RN=T2.CurrentRN1+1 AND T1.合同号=T2.合同号
UNION ALL
SELECT T1.合同号,T1.款号,T1.颜色
,CASE WHEN T1.NextNum>T2.走货数 THEN T1.NextNum ELSE T2.走货数 END
,T1.[标准总用时(只)]
,T1.CurrentRN1
,T2.RN
,CASE WHEN T1.NextNum>T2.走货数 THEN 2
WHEN T1.NextNum<T2.走货数 THEN 1
ELSE 0 END
,ABS(T1.NextNum-T2.走货数)
FROM CTE T1
JOIN CTEB T2 ON T1.NextType=2 AND T2.RN=T1.CurrentRN2+1 AND T1.合同号=T2.合同号
UNION ALL
SELECT T1.合同号,T1.款号,T1.颜色
,CASE WHEN T1.包装数>T2.走货数 THEN T2.走货数 ELSE T1.包装数 END
,T1.[标准总用时(只)]
,T1.RN
,T2.RN
,CASE WHEN T1.包装数>T2.走货数 THEN 2
WHEN T1.包装数<T2.走货数 THEN 1
ELSE 0 END
,ABS(T1.包装数-T2.走货数)
FROM CTE T0
JOIN CTEA T1 ON T0.CurrentRN1+1=T1.RN AND T0.合同号=T1.合同号 AND T0.NextType=0
JOIN CTEB T2 ON T0.CurrentRN2+1=T2.RN AND T1.合同号=T2.合同号
)
SELECT 合同号,款号,颜色,走货数,[标准总用时(只)]
FROM CTESQL2005+有效
sum()over(partition by order by )