SQL 表连接问题

[qdxjmx]

表A
店CD ，商品CD
表B
店CD ，商品CD
找出
表A里有，表B里没有的数据

[dawugui]

SELECT A.* FROM A WHERE CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) NOT IN (SELECT CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) FROM B)

[dawugui]

--同时算店CD   ，商品CD 
SELECT A.* FROM A WHERE CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) NOT IN (SELECT CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) FROM B)

--只算店CD
SELECT A.* FROM A WHERE 店CD NOT IN (SELECT 店CD FROM B)

--只算商品CD 
SELECT A.* FROM A WHERE 商品CD NOT IN (SELECT 商品CD FROM B)

[wuxinyuzhu]

select * from a n where not exists(select * from b where a.店CD=店CD and a.商品CD =商品CD 
)

[-狙击手-]

select *
from tablea a
where not exists(select 1 from tableb where a.店CD = 店CD and a.商品CD=商品CD)

[-狙击手-]

龟搞复杂了

[zhangle861010]

学习了！~~~

[hui_hui_2007]

create table 表A (
	店CD char,
	商品CD varchar(10)
)
create table 表B (
	店CD char,
	商品CD varchar(10)
)

insert into 表A (店CD,商品CD) values('1','aaaa')
insert into 表A (店CD,商品CD) values('2','bbbb')
insert into 表A (店CD,商品CD) values('3','vvvv')
insert into 表A (店CD,商品CD) values('4','dddd')
insert into 表A (店CD,商品CD) values('5','rrrr')
insert into 表A (店CD,商品CD) values('6','jjjj')


insert into 表B (店CD,商品CD) values('1','aaaa')
insert into 表B (店CD,商品CD) values('2','bbbb')
insert into 表B (店CD,商品CD) values('3','vvvv')

--结果
select *
from 表A a
where not exists(select 1 from 表B where 店CD=a.店CD and 商品CD=a.商品CD)

/*
店CD  商品CD       
---- ---------- 
4    dddd
5    rrrr
6    jjjj

（所影响的行数为 3 行）
*/