表A tblA
fItemId1 fItemId2 fQty
1 2 3
2 2 9
8 1 13
3 8 7
表B tblB
fItemId1 fItemId2 fQty
1 2 9
3 8 1
8 1 5
验证表B中 ( fItemId1 和 fItemId2)相同的记录是不是在表A中存在? 如果是单个字段,用in 就可以了,现在是两个字段,能用in来实现吗? 也就是多个字段的in 如何来实现?
fItemId1 fItemId2 fQty
1 2 3
2 2 9
8 1 13
3 8 7
表B tblB
fItemId1 fItemId2 fQty
1 2 9
3 8 1
8 1 5
验证表B中 ( fItemId1 和 fItemId2)相同的记录是不是在表A中存在? 如果是单个字段,用in 就可以了,现在是两个字段,能用in来实现吗? 也就是多个字段的in 如何来实现?
except
select * from A
---select * from b where checksum(*) not in(select checksum(*) from a)
select 1 from tableb where f1=t.f1 and f2=t.f2
)
相同用
select * from B
intersect
select * from A
---select * from b where checksum(*) in(select checksum(*) from a)
(select ltrim(fItemId1) + '!@#^$&$'+ltrim(fItemId2) from B )
select 1 from tblA where fItemId1=t.fItemId1 and fItemId2=t.fItemId2
)
declare @tblA table([fItemId1] int,[fItemId2] int,[fQty] int)
Insert @tblA
select 1,2,3 union all
select 2,2,9 union all
select 8,1,13 union all
select 3,8,7
--Select * from @tblAdeclare @tblB table([fItemId1] int,[fItemId2] int,[fQty] int)
Insert @tblB
select 1,2,9 union all
select 3,8,1 union all
select 8,1,5
--Select * from @tblBselect * from @tblB where ltrim(fItemId1) + '!@#^$&$'+ltrim(fItemId2) in
(select ltrim(fItemId1) + '!@#^$&$'+ltrim(fItemId2) from @tblA )select * from @tblB where checksum(fItemId1,fItemId2) in(select checksum(fItemId1,fItemId2) from @tblA) select * from @tblB t where exists( select 1 from @tblA where fItemId1=t.fItemId1 and fItemId2=t.fItemId2 )
/*
fItemId1 fItemId2 fQty
----------- ----------- -----------
1 2 9
3 8 1
8 1 5
*/
--2005
select * from @tblB except (select * from @tblA)
/*
fItemId1 fItemId2 fQty
----------- ----------- -----------
1 2 9
3 8 1
8 1 5
*/
declare @tblA table (fItemId1 int, fItemId2 int, fQty int)
insert into @tbla
select 1, 2, 3 union
select 2, 2, 9 union
select 8, 1, 13 union
select 3, 8, 7
declare @tblB table (fItemId1 int, fItemId2 int, fQty int)
insert into @tblb
select 1, 2, 9 union
select 3, 8, 1 union
select 8, 1, 5 select a.*
from @tbla a
inner join @tblb b on a.fItemId1=b.fItemId1 and a.fItemId2=b.fItemId2/*
fItemId1 fItemId2 fQty
----------- ----------- -----------
1 2 3
3 8 7
8 1 13(3 行受影响)
*/
SELECT A.* FROM TBA A
INNER JOIN TBB B
ON A.FITEMID1=B.FITEMID1 AND A.FITEMID2=B.FITEMID2