22,210
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#TABLE1') is null
drop table #TABLE1
Go
Create table #TABLE1([合同号] int,[业务类型] nvarchar(22),[合同金额] MONEY)
Insert #TABLE1
select 1216081201,N'合同',N'332,000.00' union all
select 16080873,N'合同',N'758,500.00'
Go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#TABLE2') is null
drop table #TABLE2
Go
Create table #TABLE2([合同号] int,[业务类型] nvarchar(22),[计划金额] MONEY)
Insert #TABLE2
select 1216081201,N'计划',N'99,600.00' union all
select 1216081201,N'计划',N'232,400.00' union all
select 16080873,N'计划',N'428,250.00' union all
select 16080873,N'计划',N'63,200.00' union all
select 16080873,N'计划',N'63,200.00' union all
select 16080873,N'计划',N'63,200.00' union all
select 16080873,N'计划',N'63,200.00' union all
select 16080873,N'计划',N'63,200.00'
Go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#TABLE3') is null
drop table #TABLE3
Go
Create table #TABLE3([合同号] int,[业务类型] nvarchar(22),[回款金额] MONEY)
Insert #TABLE3
select 1216081201,N'回款',N'199,600.00' union all
select 16080873,N'回款',N'300,000.00' union all
select 16080873,N'回款',N'-100,000.00' union all
select 16080873,N'回款',N'6,900.00' union all
select 16080873,N'回款',N'430,600.00' union all
select 16080873,N'回款',N'-5,000.00'
Go
;WITH CTEPlan
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(ORDER BY RAND()) FROM #TABLE2 --WHERE 业务类型='计划'
),CTEPlan2
AS
(
SELECT *,(SELECT SUM(计划金额) FROM CTEPlan WHERE 合同号=a.合同号 AND Ord<=a.Ord) AS Sum计划金额
FROM CTEPlan AS a
)
SELECT a.合同号,a.业务类型,a.合同金额,计划金额=0,回款金额=0,Ord=0 FROM #TABLE1 AS a --WHERE a.业务类型='合同'
UNION ALL
SELECT
a.合同号,a.业务类型,合同金额=0,a.计划金额,回款金额=CASE WHEN a.Sum计划金额<b.Sum回款金额 THEN a.计划金额
WHEN a.Sum计划金额-a.计划金额<b.Sum回款金额 THEN b.Sum回款金额-a.Sum计划金额+a.计划金额 ELSE 0 END ,a.Ord
FROM CTEPlan2 AS a
LEFT JOIN
(SELECT 合同号,SUM(回款金额) AS Sum回款金额 FROM #TABLE3 /*WHERE 业务类型='回款'*/ GROUP BY 合同号) AS b ON a.合同号=b.合同号
ORDER BY 合同号 DESC,Ord
--Ord用于排序,不显示嵌套一个结果集就行了
/*
合同号 业务类型 合同金额 计划金额 回款金额 Ord
1216081201 合同 332000.00 0.00 0.00 0
1216081201 计划 0.00 99600.00 99600.00 1
1216081201 计划 0.00 232400.00 100000.00 2
16080873 合同 758500.00 0.00 0.00 0
16080873 计划 0.00 428250.00 428250.00 3
16080873 计划 0.00 63200.00 63200.00 4
16080873 计划 0.00 63200.00 63200.00 5
16080873 计划 0.00 63200.00 63200.00 6
16080873 计划 0.00 63200.00 14650.00 7
16080873 计划 0.00 63200.00 0.00 8
*/
;WITH CTEPlan
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(ORDER BY RAND()) FROM TabName WHERE 业务类型='计划'
),CTEPlan2
AS
(
SELECT *,(SELECT SUM(计划金额) FROM CTEPlan WHERE 合同号=a.合同号 AND Ord>=a.Ord) AS Sum计划金额
FROM CTEPlan AS a
)
SELECT a.合同号,a.业务类型,a.合同金额,a.计划金额,工a.回款金额,Ord=0 FROM TabName AS a WHERE a.业务类型='合同'
UNION ALL
SELECT
a.合同号,a.业务类型,a.合同金额,a.计划金额,回款金额=CASE WHEN a.Sum计划金额<b.Sum回款金额 THEN a.计划金额
WHEN a.Sum计划金额-a.计划金额<b.Sum回款金额 THEN b.Sum回款金额-a.Sum计划金额+a.计划金额 END ,a.Ord
FROM CTEPlan2 AS a
LEFT JOIN
(SELECT 合同号,SUM(回款金额) AS Sum回款金额 FROM TabName WHERE 业务类型='回款' GROUP BY 合同号) AS b ON a.合同号=b.合同号
ORDER BY 合同号,Ord