简单问题（=及not in），高分重谢
有两个表
tb_test1
a1             a2
1               a
1               b
1               c

tb_test2
b1             b2
1               a
1               b
1               d
2               c
条件：
a1=b1且a2   not   in   b2
得到的结果应该是
tb_test1中的   1               c

------解决方案--------------------
declare @tb_test1 table(a1 int,a2 varchar(4))
insert into @tb_test1 select 1, 'a '
insert into @tb_test1 select 1, 'b '
insert into @tb_test1 select 1, 'c '

declare @tb_test2 table(b1 int,b2 varchar(4))
insert into @tb_test2 select 1, 'a '
insert into @tb_test2 select 1, 'b '
insert into @tb_test2 select 1, 'd '
insert into @tb_test2 select 2, 'c '

select
a.*
from
@tb_test1 a
where
exists(select 1 from @tb_test2 where b1=a.a1)
and
not exists(select 1 from @tb_test2 where b1=a.a1 and b2=a.a2)

/*
a1 a2
----------- ----
1 c
*/
------解决方案--------------------
another way:
select a.* from tb_test1 a left join tb_test2 b on a.a1=b.b1 and a.a2=b.b2
where b.b1 is null
------解决方案--------------------
create table tb_test1 (a1 int , a2 nvarchar(10))
insert into tb_test1 select 1 , 'a '
union select 1 , 'b '
union select 1 , 'c '

create table tb_test2(b1 int , b2 nvarchar(10))
insert into tb_test2
select 1 , 'a '
union select 1 , 'b '
union select 1 , 'd '
union select 2 , 'c '


select distinct a.*
from tb_test1 a left outer join tb_test2 b
on a.a1=b.b1
where a2 not in (select b2 from tb_test2 where a.a1=tb_test2.b1)

--result
1 c

------解决方案--------------------
select a.* from tb_test1 a,tb_test2 b where a1=b1 and a2 <> b2
------解决方案--------------------
CREATE TABLE tb_test1(a1 int,a2 nvarchar(2))
INSERT INTO tb_test1
SELECT 1 , 'a ' UNION ALL
SELECT 1 , 'b ' UNION ALL
SELECT 1 , 'c '
CREATE TABLE tb_test2(b1 int,b2 nvarchar(2))
INSERT INTO tb_test2
SELECT 1 , 'a ' UNION ALL
SELECT 1 , 'b ' UNION ALL
SELECT 1 , 'd ' UNION ALL
SELECT 2 , 'c '

SELECT * FROM tb_test1 AS A WHERE A.a2 NOT IN(SELECT b2 FROM tb_test2 AS B WHERE A.a1=B.b1)

DROP TABLE tb_test1,tb_test2

------解决方案--------------------
select distinct a.a1,a.a2 from tb_test1 a inner join tb_test2 b
on a.a1=b.b1
where a.a2 not in
(
select b2 from tb_test2 where b1=b.b1
)
------解决方案--------------------

select * from tb_test1 a
where not exists(select 1 from tb_test2 where a.a1=b1 and a.a2=b2 )