表一,ts_itemprice
字段:fitemno,fsellprice
100242, 133
100243, 128
表二,ts_itempack
字段:fpackno,fpackqty,funitprice,fdiscount
100242, 1, 59, 2
100242, 1, 39, 1
100242, 1, 39, 1 100243, 2, 28, 3
100243, 1, 10, 0
fsellprice-sum((funitprice-fdiscount)*fpackqty)应该为零。
要求得到不为零的结果。
字段:fitemno,fsellprice
100242, 133
100243, 128
表二,ts_itempack
字段:fpackno,fpackqty,funitprice,fdiscount
100242, 1, 59, 2
100242, 1, 39, 1
100242, 1, 39, 1 100243, 2, 28, 3
100243, 1, 10, 0
fsellprice-sum((funitprice-fdiscount)*fpackqty)应该为零。
要求得到不为零的结果。
select a.fitemno,a.fsellprice from ts_itemprice a left join (
select fpackno,sum((funitprice-fdiscount)*fpackqty)aa from ts_itempack group by fpackno) b
on a.fitemno=b.fpackno and a.fsellprice<>b.aa
(select fpackno,amount=sum((funitprice-fdiscount)*fpackqty) from
ts_itempack group by fpackno)b
where fitemno=b.fpackno
from ts_itemprice c,(select fpackno,amount=sum((funitprice-fdiscount)*fpackqty) from ts_itempack group by fpackno) p
where c.fitemno=p.fpackno and c.fsellprice<>p.amount
declare @ts_itemprice table(fitemno int ,fsellprice int)
insert @ts_itemprice
select 100242, 133 union all
select 100243, 128declare @ts_itempack table( fpackno int,fpackqty int ,funitprice int,fdiscount int)
insert @ts_itempack
select 100242, 1, 59, 2 union all
select 100242, 1, 39, 1 union all
select 100242, 1, 39, 1 union all
select 100243, 2, 28, 3 union all
select 100243, 1, 10, 0
select a.* from @ts_itemprice a,
(select fpackno,amount=sum((funitprice-fdiscount)*fpackqty) from
@ts_itempack group by fpackno)b
where fitemno=b.fpackno and fsellprice<>b.amount
insert into @t select '100242', 133 union all
select '100243', 128declare @a table(fpackno varchar(10),fpackqty int,funitprice int,fdiscount int)
insert into @a select '100242',1,59,2 union all
select '100242',1,39,1 union all
select '100242',1,39,1 union all
select '100243',2,28,3 union all
select '100243',1,10,0select a.fsellprice-b.che from @t a,(select fpackno,sum((funitprice-fdiscount)*fpackqty) as che from @a group by fpackno) b where a.fitemno=b.fpackno and a.fsellprice-b.che<>0