select a.customerID as 客户ID, isnull(sum(b.modvalue), 0) as 流入金额,
isnull(sum(c.modvalue), 0) as 流出金额, isnull(sum(d.fee), 0) as 话费费用, a.balance as 余额
from tbl_customer a
left join tbl_waste_book b on a.customerID = b.destID
left join tbl_waste_book c on a.customerID = c.sourceID
left join tbl_call_ticket d on a.customerID = d.customerID
group by a.customerID, a.balance
isnull(sum(c.modvalue), 0) as 流出金额, isnull(sum(d.fee), 0) as 话费费用, a.balance as 余额
from tbl_customer a
left join tbl_waste_book b on a.customerID = b.destID
left join tbl_waste_book c on a.customerID = c.sourceID
left join tbl_call_ticket d on a.customerID = d.customerID
group by a.customerID, a.balance
流出金额 =IsNULL(sum(c.modValue),0),话费总额=IsNULL(sum(d.话费费用),0),
余额 =IsNULL(sum(a.balance),0)
from tbl_customer as a
inner join tbl_waste_book as b on a.customerID=b.sourceID
inner join tbl_waste_book as c on a.customerID=c.destID
inner join tbl_call_ticket as d on a.customerID=d.customerID
group by a.customerID
客户ID=t1.customerID,
流入金额=(select sum(isnull(modValue,0)) from tbl_waste_book t2 where destID=t1.customerID),
流出金额=(select sum(isnull(modValue,0)) from tbl_waste_book t3 where sourceID=t1.customerID),
话费总额=(select sum(isnull(fee,0)) from tbl_call_ticket t4 where customerID=t1.customerID),
余额=t1.balance
from tbl_customer t1
create table tbl_customer(customerID varchar(10),balance decimal(15,2))
insert tbl_customer select 'CN001',100.0
insert tbl_customer select 'CN002',205.0
insert tbl_customer select 'CN003',15.0create table tbl_waste_book(sourceID varchar(10),destID varchar(10),modValue decimal(15,2))
insert tbl_waste_book select 'CN001','CN002',12.0
insert tbl_waste_book select 'CN003','CN001',10.5
insert tbl_waste_book select 'CN002','CN001',10.0
insert tbl_waste_book select 'CN001','CN003',2.0create table tbl_call_ticket(customerID varchar(10),fee decimal(15,2))
insert tbl_call_ticket select 'CN001',0.2
insert tbl_call_ticket select 'CN003',1.2
insert tbl_call_ticket select 'CN001',2.1
ISNULL(b.outc,0) AS 流出金额, ISNULL(d.fee,0) AS 话费总额,
(a.balance + ISNULL(c.inc,0) - ISNULL(b.outc,0) - ISNULL(d.fee,0)) AS 余额
FROM tbl_customer a
LEFT OUTER JOIN
(SELECT sourceID, SUM(Value) AS outc
FROM tbl_waste_book
GROUP BY sourceID) b
ON a.customerID=b.sourceID
LEFT OUTER JOIN
(SELECT destIDmod, SUM(Value) AS inc
FROM tbl_waste_book
GROUP BY destIDmod) c
ON a.customerID=c.destIDmod
LEFT OUTER JOIN
(SELECT customerID, SUM(fee) AS fee
FROM tbl_call_ticket
GROUP BY customerID) d
ON a.customerID=d.customerID
declare @tbl_customer table(customerid varchar(5) , balance numeric(10,1))
declare @tbl_waste_book table(sourceID varchar(5) , destID varchar(5),modValue numeric(10,1) )
declare @tbl_call_ticket table(customer varchar(5) , IDfee numeric(10,1) )insert into @tbl_customer
select 'CN001', 100.0 union
select 'CN002', 205.0 union
select 'CN003', 15.0insert into @tbl_waste_book
select 'CN001', 'CN002', 12.0 union
select 'CN003', 'CN001', 10.5 union
select 'CN002', 'CN001', 10.0 union
select 'CN001', 'CN003', 2.0
insert into @tbl_call_ticket
select 'CN001', 0.2 union
select 'CN003', 1.2 union
select 'CN001', 2.1
select a.customerid,
max(a.balance),
isnull(sum(c.IDfee),0),
(select sum(b.modValue) from @tbl_waste_book b where a.customerid = b.sourceID) ,
(select sum(b.modValue) from @tbl_waste_book b where a.customerid = b.destID) from @tbl_customer a
left outer join @tbl_call_ticket c
on a.customerid = c.customer
group by a.customerid
我是建立一个表变量
declare @tmp table (
customerID....
inflow.....
ouflow....
ticketFee...
balance....
)
然后把分别查出流入、流出、话费总额、余额都而且都放到表变量中,然后group by customerID出来总结果