两张表的数据查找
有T1和T2两张表
T1：fcompanyno,fitemno,fitemname
cqan 1300001 扳手5'寸
cqan 1300002 扳手6'寸
cqan 1300003 扳手7'寸
tjan 1300001 扳手5'寸
tjan 1300002 扳手6'寸
dlan 1300001 扳手5'寸
T2: id,fcompanyno,fitemno,fitemname，fqty,fprice,famount
1, cqan 1300001 扳手5'寸 50 13.5 675
2, cqan 1300005 扳手9'寸 40 12 480
3, tjan 1300002 扳手6'寸 30 12 360
4, tjan 1300006 扳手7'寸 60 15 900
需要查找fcompanyno,fitemno在T2中存在记录，但是在fcompanyno,fitemno中没有的数据
求SQL语句，要求查询结果为：
cqan 1300005 扳手9'寸
tjan 1300006 扳手7'寸
[解决办法]
create table T1(fcompanyno varchar(10),fitemno varchar(10),fitemname varchar(10))
insert into T1
select 'cqan',1300001,'扳手5''寸'
union all select 'cqan',1300002,'扳手6''寸'
union all select 'cqan',1300003,'扳手7''寸'
union all select 'tjan',1300001,'扳手5''寸'
union all select 'tjan',1300002,'扳手6''寸'
union all select 'dlan',1300001,'扳手5''寸'
create table T2(id int,fcompanyno varchar(10),fitemno varchar(10),fitemname varchar(10),
fqty int ,fprice numeric(12,2),famount numeric(12,2))
insert into T2
select 1,'cqan',1300001,'扳手5''寸',50,13.5,675
union all select 2,'cqan',1300005,'扳手9''寸',40,12,480
union all select 3,'tjan',1300002,'扳手6''寸',30,12,360
union all select 4,'tjan',1300006,'扳手7''寸',60,15,900
select fcompanyno,fitemno,fitemname
from T2
where not exists(select 1 from T1 where T1.fcompanyno=T2.fcompanyno and T1.fitemno=T2.fitemno)
drop table T1,T2
/*
fcompanynofitemnofitemname
cqan1300005扳手9'寸
tjan1300006扳手7'寸
*/
if OBJECT_ID('tempdb..#temp1', 'u') is not null drop table #temp1;
go
create table #temp1( [fcompanyno] varchar(100), [fitemno] varchar(100), [fitemname] varchar(100));
insert #temp1
select 'cqan','1300001','扳手5寸' union all
select 'cqan','1300002','扳手6寸' union all
select 'cqan','1300003','扳手7寸' union all
select 'tjan','1300001','扳手5寸' union all
select 'tjan','1300002','扳手6寸' union all
select 'dlan','1300001','扳手5寸'
--SQL:
if OBJECT_ID('tempdb..#temp2', 'u') is not null drop table #temp2;
go
create table #temp2( [id] varchar(100), [fcompanyno] varchar(100), [fitemno] varchar(100), [fitemname] varchar(100), [fqty] varchar(100), [fprice] varchar(100), [famount] varchar(100));
insert #temp2
select '1','cqan','1300001','扳手5寸','50','13.5','675' union all
select '2','cqan','1300005','扳手9寸','40','12','480' union all
select '3','tjan','1300002','扳手6寸','30','12','360' union all
select '4','tjan','1300006','扳手7寸','60','15','900'
--SQL:
SELECT m.fcompanyno, m.fitemno, m.fitemname FROM #temp2 m
INNER JOIN
(
select * from #temp1 a
WHERE EXISTS
(
SELECT 1
FROM #temp2 b
WHERE b.fcompanyno = a.fcompanyno
AND b.fitemno = a.fitemno
)
) n
ON m.fcompanyno = n.fcompanyno
AND m.fitemno <> n.fitemno
/*
fcompanynofitemnofitemname
cqan1300005扳手9寸
tjan1300006扳手7寸
*/