|
#2
qazzwj2008-08-16 15:53
declare @TblMess table(MessNo char(3),Mess char(20))
declare @SsTable table(SsNo char(20),Mess char(20)) declare @SsList table(SsNo char(20),Quantity smallint,Price int,Money int) declare @SrTable table(SrNo char(20),SsNo char(20),Mess char(20)) declare @SrList table(SrNo char(20),SsNo char(20),Quantity smallint,Price int,Money int) insert into @TblMess values('001','客户A') insert into @TblMess values('002','客户B') insert into @TblMess values('003','客户C') insert into @SsTable values('Ss0808-008','客户A') insert into @SsList values('Ss0808-008',2,130,260) insert into @SsList values('Ss0808-008',8,120,960) insert into @SrTable values('SR0808-001','Ss0808-008','客户A') insert into @SrList values('SR0808-001','Ss0808-008',1,130,130) select a.MessNo,a.Mess,isnull(e.SaleTotalMoney,0)SaleTotalMoney,isnull(f.SaleReturnMoney,0) SaleReturnMoney,isnull(e.SaleTotalMoney-f.SaleReturnMoney,0) LastTotalMoney from @TblMess a left join @SsTable b on a.Mess=b.Mess left join (select SsNo,SUM(Money) SaleTotalMoney from @SsList group by SsNo)e on b.SsNo=e.SsNo left join (select d.SsNo,d.Money SaleReturnMoney from @SrTable c join @SrList d on c.SrNo=d.SrNo) f on b.SsNo=f.SsNo order by a.MessNo |
求一ＳＱＬ ＳＥＲＶＥＲ语句
TblMess客户表
MessNo Mess
001 客户Ａ
002 客户Ｂ
003 客户Ｃ
SsTable(销售发货表)
SsNo SsDate Mess Makeorder
Ss0808-008 2008-8-1 客户Ａ Admin
SsList(销售发货明细表)
SsNo WareNo WareName Unit Quantity Price Money
Ss0808-008 F01 内存条 条 2 130 260
Ss0808-008 F02 光驱 台 8 120 960
SrTable(销售退货)
SrNo SrDate Mess MakeOrder
SR0808-001 2008-8-10 客户Ａ Admin
SrList(销售退货明细表)
SrNo SsNo SsDate WareNo WareName Unit Quantity Price Money
Sr0808-001 Sr0808-008 2008-8-1 F01 内存条 条 1 130 130
我要的结果是这样的
MessNo Mess SaleTotalMoney SaleReturnMoney LastTotalMoney
001 客户Ａ 1220 130 1090
002 客户Ｂ 0 0 0
003 客户Ｃ 0 0 0