A表
合同号 品名 价格 数量
mn011-1 aaa 1 100
mn011-1 bb 2 100
mn011-1 cc 1 300B表
合同号 到账金额 到账日期
mn011-1 400 2010-12-21
mn011-1 100 2010-12-31A表里是这个合同里卖出的产品,B表是客户付款的总额
现在要统计出A表里货物的总额-付款的总额大于50的记录合同号
合同号 品名 价格 数量
mn011-1 aaa 1 100
mn011-1 bb 2 100
mn011-1 cc 1 300B表
合同号 到账金额 到账日期
mn011-1 400 2010-12-21
mn011-1 100 2010-12-31A表里是这个合同里卖出的产品,B表是客户付款的总额
现在要统计出A表里货物的总额-付款的总额大于50的记录合同号
from
(select 合同号,num=sum(价格*数量) from ta group by 合同号
)a,
(select 合同号,num=sum(到账金额) from tb group by 合同号
)b
where a.合同号=b.合同号 and a.num-b.num>50
from
(
select 合同号,sum(价格 * 数量) as 金额 from a group by 合同号
)t1 left join
(
select 合同号,sum(到账金额) as 到账金额 from b group by 合同号
)t2 on t1.合同号 = t2.合同号
where t1.金额 - isnull(t2.到账金额,0) > 50
create table A
(
contactId varchar(20),
contactName varchar(30),
price float,
number int
)
drop table B
create table B
(
contactId varchar(20),
moneys float,
entrydate datetime
)insert into A values('mn011-1', 'aaa', 1, 100)
insert into A values('mn011-1', 'bb', 2, 100)
insert into A values('mn011-1', 'cc', 1, 300)
insert into A values('mn011-2', 'cc', 1, 300)
insert into A values('mn011-2', 'dd', 1, 100)
insert into A values('mn011-3', 'ee', 1, 300)
insert into A values('mn011-3', 'ff', 1, 100)insert into B values('mn011-1', 400,'2010-12-21')
insert into B values('mn011-1', 100,'2010-12-31')
insert into B values('mn011-2', 300,'2010-12-31')
insert into B values('mn011-3', 350,'2010-12-31')select a.contactId ,isnull(a.moneys,0)-isnull(b.moneys,0) as delaymoney
from
(select sum(isnull(price,0)*isnull(number,0)) as moneys,contactId from A group by contactId) a
inner join
(select sum(isnull(moneys,0)) moneys,contactId from B group by contactId) b on a.contactId=b.contactId
where isnull(a.moneys,0)-isnull(b.moneys,0) > 50
create table t1
(
chetonghao varchar(10),
cpinming varchar(10),
ijiage int,
ishuliang int
)
insert into t1
select 'mn011-1', 'aaa', 1, 100 union all
select 'mn011-1', 'bb', 2, 100 union all
select 'mn011-1', 'cc', 1, 300
create table t2
(
chetonghao varchar(10),
idaozhangjine int,
ddaozhangriqi varchar(10)
)
insert into t2
select 'mn011-1', 400, '2010-12-21' union all
select 'mn011-1', 100, '2010-12-31';with abc as
(select a.chetonghao,jine1,jine2 from
(select chetonghao,sum(ijiage*ishuliang) as jine1 from t1 group by chetonghao) a inner join
(select chetonghao,sum(idaozhangjine) as jine2 from t2 group by chetonghao) b on a.chetonghao=b.chetonghao)
select * from abc where jine1>jine2+50