我现在有一张表如下:
boxNo itemID qty auxQtya01 1101 15 0
a01 1102 10 0
b01 1101 15 0
b01 1104 15 0
b01 1105 15 0
b01 1106 19 0
a01 1101 0 15
a01 1102 0 20
b01 1101 0 15
b01 1105 0 19
b01 1106 0 19
我现在要从这张表中去除如下数据
select distinct t1.boxNo,t1.ItemID,t1.qty,t1.auxqty
from overAll t1 inner join overAll t2 on
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty
即:
a01 1101 0 15
a01 1101 15 0
b01 1101 0 15
b01 1101 15 0
b01 1106 0 19
b01 1106 19 0
请问有什么办法实现。
boxNo itemID qty auxQtya01 1101 15 0
a01 1102 10 0
b01 1101 15 0
b01 1104 15 0
b01 1105 15 0
b01 1106 19 0
a01 1101 0 15
a01 1102 0 20
b01 1101 0 15
b01 1105 0 19
b01 1106 0 19
我现在要从这张表中去除如下数据
select distinct t1.boxNo,t1.ItemID,t1.qty,t1.auxqty
from overAll t1 inner join overAll t2 on
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty
即:
a01 1101 0 15
a01 1101 15 0
b01 1101 0 15
b01 1101 15 0
b01 1106 0 19
b01 1106 19 0
请问有什么办法实现。
inner join (
select distinct t1.boxNo,t1.ItemID,t1.qty,t1.auxqty
from overAll t1 inner join overAll t2 on
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty)t on overAll.boxNO=t.boxNO and overAll.itemid=t.itemid and overAll.qty=t.qty and overAll.auxqty=t.auxqty
go
create table overAll(boxNO varchar(10),itemid int,qty int,auxqty int)
insert into overAll
select 'a01',1101,15,0
union all select 'a01',1102,10,0
union all select 'b01',1101,15,0
union all select 'b01',1104,15,0
union all select 'b01',1105,15,0
union all select 'b01',1106,19,0
union all select 'a01',1101,0,15
union all select 'a01',1102,0,20
union all select 'b01',1101,0,15
union all select 'b01',1105,0,19
union all select 'b01',1106,0,19delete overAll from overAll
inner join (
select distinct t1.boxNo,t1.ItemID,t1.qty,t1.auxqty
from overAll t1 inner join overAll t2 on
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty)t on overAll.boxNO=t.boxNO and overAll.itemid=t.itemid and overAll.qty=t.qty and overAll.auxqty=t.auxqtyselect * from overAll
/*
boxNO itemid qty auxqty
---------- ----------- ----------- -----------
a01 1102 10 0
b01 1104 15 0
b01 1105 15 0
a01 1102 0 20
b01 1105 0 19(所影响的行数为 5 行)
*/
delete from overAll
from overAll t1 inner join overAll t2 on
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty)
create table overAll(boxNO varchar(10),itemid int,qty int,auxqty int)
insert into overAll
select 'a01',1101,15,0
union all select 'a01',1102,10,0
union all select 'b01',1101,15,0
union all select 'b01',1104,15,0
union all select 'b01',1105,15,0
union all select 'b01',1106,19,0
union all select 'a01',1101,0,15
union all select 'a01',1102,0,20
union all select 'b01',1101,0,15
union all select 'b01',1105,0,19
union all select 'b01',1106,0,19delete from t1
from overAll as t1 inner join overAll as t2 on
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty
select * from overAll
--结果
boxNO itemid qty auxqty
---------- ----------- ----------- -----------
a01 1102 10 0
b01 1104 15 0
b01 1105 15 0
a01 1102 0 20
b01 1105 0 19(5 行受影响)
from overAll t1 where not exists(select 1 from overAll t2 where
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty)
from overAll t1 where not exists(select 1 from overAll t2 where
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty)
谢谢。就是这个
where not exists(select 1 from (select distinct t1.boxNo,t1.ItemID,t1.qty,t1.auxqty
from overAll t1 inner join overAll t2 on
t1.boxno=t2.boxno and t1.itemid=t2.itemid
and t1.qty=t2.auxqty and t2.qty=t1.auxqty
) b where a.boxno=b.boxNo and a.ItemID=b.ItemID and a.qty=b.qty and a.auxqty = b.auxqty)