A 表ID Contractno
1 111
2 111
3 112
4 112
B 表ID Contractno productno price num
1 111 1 2.0 50
2 111 2 5.0 150
3 111 3 3.0 500
4 112 1 2.0 50
5 112 2 3.0 50
6 113 1 2.0 50
C 表 ID Contractno backprice
1 111 1500
2 111 100
3 112 250
现在要在A表中统计出B表price*num不等于C表中backprice的单号
1 111
2 111
3 112
4 112
B 表ID Contractno productno price num
1 111 1 2.0 50
2 111 2 5.0 150
3 111 3 3.0 500
4 112 1 2.0 50
5 112 2 3.0 50
6 113 1 2.0 50
C 表 ID Contractno backprice
1 111 1500
2 111 100
3 112 250
现在要在A表中统计出B表price*num不等于C表中backprice的单号
WHERE ID IN(SELECT T2.ID FROM (SELECT ID,price*num AS PRICE_NUM FROM B) T2,C T3 WHERE T2.IDENTITY=T3.ID AND T2.PRICE_NUM<>T3.backprice)
WHERE ID IN
(SELECT T2.ID FROM (SELECT ID,price*num AS PRICE_NUM FROM B) T2,C T3
WHERE T2.ID=T3.ID AND T2.PRICE_NUM<>T3.backprice)
结果如下:
Contractno
111
WHERE Contractno IN
(SELECT T2.Contractno FROM (SELECT Contractno,price*num AS PRICE_NUM FROM B) T2,C T3
WHERE T2.Contractno=Contractno.ID AND T2.PRICE_NUM<>T3.backprice)
create table a(ID INT,Contractno VARCHAR(20))
create table b(ID INT,Contractno VARCHAR(20),productno VARCHAR(20),price MONEY,NUM INT)
create table c(ID INT,Contractno VARCHAR(20),backprice money)--插入数据
insert into a(id,Contractno)
select 1,'111'
union all select 2,'111'
union all select 3,'112'
union all select 4,'112'
insert into b(ID,Contractno,productno,price,num)
select 1,'111',1,2.0,50
union all select 2,'111',2,5.0,150
union all select 3,'111',3,3.0,500
union all select 4,'112',1,2.0,50
union all select 5,'112',2,3.0,50
union all select 6,'113',1,2.0,50insert into c(ID,Contractno,backprice)
select 1,'111',1500
union all select 2,'111',100
union all select 3,'111',250
--统计查询
SELECT T1.Contractno FROM
(SELECT Contractno,price*num AS PRICE_NUM FROM B) T1,
(SELECT Contractno,backprice FROM C) T2
WHERE T1.PRICE_NUM<>T2.backprice AND T1.Contractno=T2.Contractno
(SELECT Contractno,price*num AS PRICE_NUM FROM B) T1,
(SELECT Contractno,backprice FROM C) T2
WHERE T1.PRICE_NUM<>T2.backprice AND T1.Contractno=T2.Contractno
[/code]
没问题~测试完毕
WHERE Contractno IN(
SELECT DISTINCT T1.Contractno FROM
(SELECT Contractno,price*num AS PRICE_NUM FROM B) T1,
(SELECT Contractno,backprice FROM C) T2
WHERE T1.PRICE_NUM<>T2.backprice AND T1.Contractno=T2.Contractno)
--借用13楼数据select b.* from b left join (select b.*
from b
join c on b.Contractno=c.Contractno and b.NUM*b.price=c.backprice
)bb on b.Contractno=bb.Contractno and b.productno=bb.productno
where bb.Contractno is null