求一个SQL 语句 在线等！

[castlooo]

BillExtDetail（收入表）
----------------------------————
cCusCode | gBusiExtract | cInvDep|
---------------------------—————
001 1000 部门1
--------------------------------------
001 200 部门2
-------------------------------------
001 300 部门1
------------------------------------



PayOutItem（支出表）
----------------------------——
cCusCode | outFee | cInvDep|
---------------------------——
001 800 部门1
-------------------------------
001 300 部门3


要求结果：要求结果：要求结果：要求结果：要求结果：要求结果：要求结果：
阐述 1、如果这个部门有收入那么从收入表中汇总并减 得出 剩余总额
2、如果这个部门没有收入数据，那么从汇总显示 得出负数
特别是没有收入数据的
没有收入数据没有收入数据的
部门名称 收入总额 支出总额 剩余总额
————————————————————————————————
部门1 1300 800 500
————————————————————————————————
部门2 200 0 200
----------------------------------------------------------------
部门3 0 300 -300
----------------------------------------------------------------

[castlooo]

---测试数据---------------------------------
declare @BillExtDetail table(cCusCode char(3),gBusiExtract int,cInvDep varchar(5))
insert @BillExtDetail
select '001',1000,'部门1'
union all
select '001',200,'部门2'
union all
select '001',300,'部门1'


declare @PayOutItem table(cCusCode char(3),outFee int,cInvDep varchar(5))
insert @PayOutItem
select '001',800,'部门1'

------查询-------------------------------
select a.cInvDep 部门名称,
isnull(a.gBusiExtract,0) 收入总额,
isnull(b.outFee,0) 支出总额,
isnull(a.gBusiExtract,0)-isnull(b.outFee,0) 剩余总额
from (select cInvDep,
sum(gBusiExtract) gBusiExtract
from @BillExtDetail
group by cInvDep) a
left outer join
(select cInvDep,
sum(outFee) outFee
from @PayOutItem
Group by cInvDep) b
on a.cInvDep = b.cInvDep
---结果---------------------------------
-- 部门名称 收入总额 支出总额 剩余总额
-- ----- ----------- ----------- -----------
-- 部门1 1300 800 500
-- 部门2 200 0 200
--
-- （所影响的行数为 2 行）


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++===============================********************************************************************************************
不能实现 ：：如果这个部门没有收入数据，那么从汇总显示 得出负数

***********************************************************
——————————————+——+——++——+——+——+——+——+——

[akuzou]

declare @BillExtDetail table(cCusCode char(3),gBusiExtract int,cInvDep varchar(5))
insert @BillExtDetail
select '001',1000,'部门1'
union all
select '001',200,'部门2'
union all
select '001',300,'部门1'


declare @PayOutItem table(cCusCode char(3),outFee int,cInvDep varchar(5))
insert @PayOutItem
select '001',800,'部门1'
union all select '001',300,'部门3'

------查询-------------------------------
select isnull(a.cInvDep,b.cInvDep) 部门名称,
isnull(a.gBusiExtract,0) 收入总额,
isnull(b.outFee,0) 支出总额,
isnull(a.gBusiExtract,0)-isnull(b.outFee,0) 剩余总额
from (select cInvDep,
sum(gBusiExtract) gBusiExtract
from @BillExtDetail
group by cInvDep) a
full join
(select cInvDep,
sum(outFee) outFee
from @PayOutItem
Group by cInvDep) b
on a.cInvDep = b.cInvDep
--结果
部门名称 收入总额 支出总额 剩余总额
----- ----------- ----------- -----------
部门1 1300 800 500
部门2 200 0 200
部门3 0 300 -300

（所影响的行数为 3 行）

[子陌红尘]

select
部门名称=a.cInvDep,
收入总额=isnull(b.gBusiExtract,0),
支出总额=isnull(c.outFee,0),
剩余总额=isnull(b.gBusiExtract,0)-isnull(c.outFee,0)
from
(select distinct cInvDep from BillExtDetail
union
select distinct cInvDep from PayOutItem) a
left join
(select cInvDep,sum(gBusiExtract) as gBusiExtract from BillExtDetail group by cInvDep) b
on
a.cInvDep=b.cInvDep
left join
(select cInvDep,sum(outFee) as outFee from PayOutItem group by cInvDep) c
on
a.cInvDep=c.cInvDep
order by
a.cInvDep

[子陌红尘]

declare @BillExtDetail table(cCusCode char(3),gBusiExtract int,cInvDep varchar(5))
insert @BillExtDetail
select '001',1000,'部门1'
union all
select '001',200,'部门2'
union all
select '001',300,'部门1'


declare @PayOutItem table(cCusCode char(3),outFee int,cInvDep varchar(5))
insert @PayOutItem
select '001',800,'部门1' union
select '001',300,'部门3'


select
部门名称=a.cInvDep,
收入总额=isnull(b.gBusiExtract,0),
支出总额=isnull(c.outFee,0),
剩余总额=isnull(b.gBusiExtract,0)-isnull(c.outFee,0)
from
(select distinct cInvDep from @BillExtDetail
union
select distinct cInvDep from @PayOutItem) a
left join
(select cInvDep,sum(gBusiExtract) as gBusiExtract from @BillExtDetail group by cInvDep) b
on
a.cInvDep=b.cInvDep
left join
(select cInvDep,sum(outFee) as outFee from @PayOutItem group by cInvDep) c
on
a.cInvDep=c.cInvDep
order by
a.cInvDep


/*
部门名称 收入总额 支出总额 剩余总额
-------- ----------- ----------- -----------
部门1 1300 800 500
部门2 200 0 200
部门3 0 300 -300
*/

[w75251455]

create table 收入表( cCusCode varchar(5), gBusiExtract int, cInvDep varchar(5))
insert 收入表 select '001', 1000, '部门1'
union all select '001', 200, '部门2'
union all select '001', 300, '部门1'



create table 支出表(cCusCode varchar(5), outFee int, cInvDep varchar(5))
insert 支出表 select '001', 800, '部门1'
union all select '001', 300, '部门3'


select *,(收入总额+支出总额)剩余总额 from
(select isnull(a.cInvDep,b.cInvDep)部门名称,
isnull(a.gBusiExtract,0)收入总额,
isnull(b.outFee*-1,0)支出总额
from (select sum(gBusiExtract)gBusiExtract,cInvDep from 收入表 group by cInvDep)a
full join 支出表 b on a.cInvDep=b.cInvDep)a

[中国风]

declare @BillExtDetail table(cCusCode char(3),gBusiExtract int,cInvDep varchar(5))
insert @BillExtDetail
select '001',1000,'部门1'
union all
select '001',200,'部门2'
union all
select '001',300,'部门1'


declare @PayOutItem table(cCusCode char(3),outFee int,cInvDep varchar(5))
insert @PayOutItem
select '001',800,'部门1'
union all select '001',300,'部门3'

select [部门名称]=isnull(a.cInvDep,b.cInvDep),
[收入]=isnull(a.gBusiExtract,0),
[支出]=isnull(a.outFee,0),
[剩余总额]=isnull(a.gBusiExtract,0)-isnull(a.outFee,0)
from @BillExtDetail a full join @PayOutItem b on a.cInvDep=b.cInvDep

[w75251455]

部门名称 收入总额 支出总额 剩余总额
————————————————————————————————
部门1 1300 800 500
————————————————————————————————
部门2 200 0 200
----------------------------------------------------------------
部门3 0 300 -300

[w75251455]

create table 收入表( cCusCode varchar(5), gBusiExtract int, cInvDep varchar(5))
insert 收入表 select '001', 1000, '部门1'
union all select '001', 200, '部门2'
union all select '001', 300, '部门1'



create table 支出表(cCusCode varchar(5), outFee int, cInvDep varchar(5))
insert 支出表 select '001', 800, '部门1'
union all select '001', 300, '部门3'


select *,(收入总额-支出总额)剩余总额 from
(select isnull(a.cInvDep,b.cInvDep)部门名称,
isnull(a.gBusiExtract,0)收入总额,
isnull(b.outFee*,0)支出总额
from (select sum(gBusiExtract)gBusiExtract,cInvDep from 收入表 group by cInvDep)a
full join 支出表 b on a.cInvDep=b.cInvDep)a

[castlooo]

在看！

[中国风]

declare @BillExtDetail table(cCusCode char(3),gBusiExtract int,cInvDep varchar(5))
insert @BillExtDetail
select '001',1000,'部门1'
union all
select '001',200,'部门2'
union all
select '001',300,'部门1'


declare @PayOutItem table(cCusCode char(3),outFee int,cInvDep varchar(5))
insert @PayOutItem
select '001',800,'部门1'
union all select '001',300,'部门3'

select [部门名称]=isnull(a.cInvDep,b.cInvDep),
[收入]=isnull((select sum(isnull(gBusiExtract,0)) from @BillExtDetail where cInvDep=a.cInvDep),0),
[支出]=isnull((select sum(isnull(outFee,0)) from @PayOutItem where cInvDep=b.cInvDep),0),
[剩余总额]=isnull((select sum(isnull(gBusiExtract,0)) from @BillExtDetail where cInvDep=a.cInvDep),0)-
isnull((select sum(isnull(outFee,0)) from @PayOutItem where cInvDep=b.cInvDep),0)
from @BillExtDetail a full join @PayOutItem b on a.cInvDep=b.cInvDep
group by a.cInvDep,b.cInvDep order by 部门名称 asc

(3 行受影响)

(2 行受影响)
部门名称 收入 支出 剩余总额
----- ----------- ----------- -----------
部门1 1300 800 500
部门2 200 0 200
部门3 0 300 -300

(3 行受影响)

[castlooo]

Top
libin_ftsafe(子陌红尘：当libin告别ftsafe) (
正解！

[castlooo]

roy_88(中国风_燃烧你的激情！！！)

正解！
正解！
正解！
正解！