590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([ContractID] nvarchar(28),[VersionNo] int,[ProductCode] nvarchar(40),[Sequence] int,[Totalamount] int)
Insert #T1
select N'CS180195',1,N'42CN00530CALB',1,100 union all
select N'CS180195',1,N'4242CN00530CARB',2,200 union all
select N'CS180195',1,N'424230RQ130ACCSETBHS',3,300 union all
select N'CS180195',2,N'424230RQ130ACCSETBHS',1,50 union all
select N'CS180195',2,N'424230RQ130ACCSETBH1',2,500 union all
select N'CS180195',2,N'424230RQ130ACCSETBH2',3,600 union all
select N'CS180195',2,N'424230RQ130ACCSETBH3',4,700 union all
select N'CS180195',2,N'424230RQ130ACCSETBH4',5,800 union all
select N'CS180195',3,N'424230RQ130ACCSETBHS',1,10 union all
select N'CS180195',3,N'424230RQ130ACCSETBH1',2,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH2',3,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH3',4,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH4',5,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH4',6,400
Go
--测试数据结束
SELECT * ,
Totalamount AS [difference]
FROM #T1 WHERE VersionNo=1
UNION ALL
SELECT #T11.* ,
CASE WHEN #T12.Totalamount IS NOT NULL
THEN #T11.Totalamount - #T12.Totalamount
ELSE #T11.Totalamount
END AS [difference]
FROM #T1 AS #T11
LEFT JOIN #T1 AS #T12 ON #T11.[Sequence] = #T12.[Sequence]
AND #T11.ContractID = #T12.ContractID
AND #T12.VersionNo=1
WHERE #T11.VersionNo=2
UNION ALL
SELECT #T13.* ,
CASE WHEN #T14.Totalamount IS NOT NULL
THEN #T13.Totalamount - #T14.Totalamount
ELSE #T13.Totalamount
END AS [difference]
FROM #T1 AS #T13
LEFT JOIN #T1 AS #T14 ON #T13.[Sequence] = #T14.[Sequence]
AND #T14.ContractID = #T13.ContractID
AND #T14.VersionNo=2
WHERE #T13.VersionNo=3
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([ContractID] nvarchar(28),[VersionNo] int,[ProductCode] nvarchar(40),[Sequence] int,[Totalamount] int)
Insert #T1
select N'CS180195',1,N'42CN00530CALB',1,100 union all
select N'CS180195',1,N'4242CN00530CARB',2,200 union all
select N'CS180195',1,N'424230RQ130ACCSETBHS',3,300
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([ContractID] nvarchar(28),[VersionNo] int,[ProductCode] nvarchar(40),[Sequence] int,[Totalamount] int)
Insert #T2
select N'CS180195',2,N'424230RQ130ACCSETBHS',1,50 union all
select N'CS180195',2,N'424230RQ130ACCSETBH1',2,500 union all
select N'CS180195',2,N'424230RQ130ACCSETBH2',3,600 union all
select N'CS180195',2,N'424230RQ130ACCSETBH3',4,700 union all
select N'CS180195',2,N'424230RQ130ACCSETBH4',5,800
Go
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
Create table #T3([ContractID] nvarchar(28),[VersionNo] int,[ProductCode] nvarchar(40),[Sequence] int,[Totalamount] int)
Insert #T3
select N'CS180195',3,N'424230RQ130ACCSETBHS',1,10 union all
select N'CS180195',3,N'424230RQ130ACCSETBH1',2,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH2',3,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH3',4,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH4',5,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH4',6,400
Go
--测试数据结束
SELECT * ,
Totalamount AS [difference]
FROM #T1
UNION ALL
SELECT #T2.* ,
CASE WHEN #T1.Totalamount IS NOT NULL
THEN #T2.Totalamount - #T1.Totalamount
ELSE #T2.Totalamount
END AS [difference]
FROM #T2
LEFT JOIN #T1 ON #T1.[Sequence] = #T2.[Sequence]
AND #T1.ContractID = #T2.ContractID
UNION ALL
SELECT #T3.* ,
CASE WHEN #T2.Totalamount IS NOT NULL
THEN #T3.Totalamount - #T2.Totalamount
ELSE #T3.Totalamount
END AS [difference]
FROM #T3
LEFT JOIN #T2 ON #T3.[Sequence] = #T2.[Sequence]
AND #T2.ContractID = #T3.ContractID
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([ContractID] nvarchar(28),[VersionNo] int,[ProductCode] nvarchar(40),[Sequence] int,[Totalamount] int)
Insert #T1
select N'CS180195',1,N'42CN00530CALB',1,100 union all
select N'CS180195',1,N'4242CN00530CARB',2,200 union all
select N'CS180195',1,N'424230RQ130ACCSETBHS',3,300
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([ContractID] nvarchar(28),[VersionNo] int,[ProductCode] nvarchar(40),[Sequence] int,[Totalamount] int)
Insert #T2
select N'CS180195',2,N'424230RQ130ACCSETBHS',1,50 union all
select N'CS180195',2,N'424230RQ130ACCSETBH1',2,500 union all
select N'CS180195',2,N'424230RQ130ACCSETBH2',3,600 union all
select N'CS180195',2,N'424230RQ130ACCSETBH3',4,700 union all
select N'CS180195',2,N'424230RQ130ACCSETBH4',5,800
Go
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
Create table #T3([ContractID] nvarchar(28),[VersionNo] int,[ProductCode] nvarchar(40),[Sequence] int,[Totalamount] int)
Insert #T3
select N'CS180195',3,N'424230RQ130ACCSETBHS',1,10 union all
select N'CS180195',3,N'424230RQ130ACCSETBH1',2,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH2',3,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH3',4,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH4',5,400 union all
select N'CS180195',3,N'424230RQ130ACCSETBH4',6,400
Go
--测试数据结束
--1、
SELECT * ,
Totalamount AS [difference]
FROM #T1
--2、
SELECT #T2.* ,
CASE WHEN #T1.Totalamount IS NOT NULL
THEN #T2.Totalamount - #T1.Totalamount
ELSE #T2.Totalamount
END AS [difference]
FROM #T2
LEFT JOIN #T1 ON #T1.[Sequence] = #T2.[Sequence]
AND #T1.ContractID = #T2.ContractID
--3、
SELECT #T3.* ,
CASE WHEN #T2.Totalamount IS NOT NULL
THEN #T3.Totalamount - #T2.Totalamount
ELSE #T3.Totalamount
END AS [difference]
FROM #T3
LEFT JOIN #T2 ON #T3.[Sequence] = #T2.[Sequence]
AND #T2.ContractID = #T3.ContractID