--既然你分开统计已经写出来,就这样合并select 单位=isnull(a.单位,isnull(b.单位,c.单位))
,c1,c2,c3,c4
from(
--c1的查询语句
select ...
)a full join(
--c2的查询语句
select ...
)b on a.单位=b.单位 full join(
--c3的查询语句
select ...
)c on a.单位=c.单位 full join(
--c4的查询语句
select ...
)d on a.单位=d.单位
,c1,c2,c3,c4
from(
--c1的查询语句
select ...
)a full join(
--c2的查询语句
select ...
)b on a.单位=b.单位 full join(
--c3的查询语句
select ...
)c on a.单位=c.单位 full join(
--c4的查询语句
select ...
)d on a.单位=d.单位
SELECT 'A', '2000-02-01', '200'
UNION ALL SELECT 'A', '2000-02-01', '200'
UNION ALL SELECT 'A', '2000-04-01', '200'
UNION ALL SELECT 'A', '2000-02-02', '200'
UNION ALL SELECT 'A', '2000-03-02', '200'UNION ALL SELECT 'B', '2000-02-01', '200'
UNION ALL SELECT 'B', '2000-04-01', '2100'
UNION ALL SELECT 'B', '2000-02-04', '200'
UNION ALL SELECT 'B', '2000-03-02', '2030'Declare @表B Table (单位 varchar(10), 日期 datetime, 回收金额 decimal(18, 6))
INSERT INTO @表B
SELECT 'A', '2000-02-01', '200'
UNION ALL SELECT 'A', '2000-02-01', '100'
UNION ALL SELECT 'A', '2000-04-01', '200'
UNION ALL SELECT 'A', '2000-02-02', 50
UNION ALL SELECT 'A', '2000-03-02', 10
UNION ALL SELECT 'B', '2000-03-02', 30Declare @表C Table (单位 varchar(10), 赊销额度 int, 赊销期限 int)INSERT INTO @表C
SELECT 'A', 100, 5
UNION ALL SELECT 'B', 700, 15Declare @T DatetimeSet @T = '2000-03-01'--- 以上是初始化數據
--- C4 (超赊销期限金额) 沒能領會意思
SELECT T_Temp.单位,
Sum(发货金额 - 回收金额 ) as 欠款,
Sum(发货金额 - 回收金额 ) - IsNull(
(SELECT 赊销额度 FROM @表C as c where c.单位 = T_temp.单位 ), 0) as 超赊销额度金额
FROM
(
SELECT a.单位, a.日期,
sum( a.发货金额) as 发货金额,
IsNull((SELECT sum( b.回收金额) as 回收金额
FROM @表b as b
WHERE a.单位 = b.单位 and a.日期 = b.日期
group by b.单位, b.日期) , 0.00) as 回收金额
FROM @表A as a
WHERE DateDiff (day , a.日期 , @T) > (SELECT 赊销期限 FROM @表C as c where c.单位 = a.单位 )
Group by a.单位, a.日期
HAVING sum( a.发货金额) - IsNull((Select sum( b.回收金额) as 回收金额 FROM @表b as b
where a.单位 = b.单位 and a.日期 = b.日期
group by b.单位, b.日期) , 0.00)
> IsNull((SELECT 赊销额度 FROM @表C as c where c.单位 = a.单位 ), 0)
) as T_Temp
Group By T_Temp.单位