求一SQL迅速查出A表在B表中不存在的数据。

[dljinsui]

总共有两个表，A与B表，A表记录每个人拥有的类型，B表中类型基础表，求一SQL迅速查出A表中当前这个人没有的类型。



A表，每个人拥有的类型

字段：
a,
b_type.

纪录
-------------------------------------------
a b_type
-------------------------------------------
张三 A01
张三 A02
张三 A05
李四 A01
李四 A03
李四 A05
-------------------------------------------

B表，类型基础表
字段：
b_type

纪录
-------------------------------------------
b_type
-------------------------------------------
A01
A02
A03
A04
A05
-------------------------------------------


求SQL能实现如下结果。

纪录
-------------------------------------------
a b
-------------------------------------------
张三 A03
张三 A04
李四 A02
李四 A04
-------------------------------------------

[专注or全面]

CREATE TABLE #T1
(
  NAME VARCHAR(50),
  TYPE VARCHAR(10)
)
INSERT INTO #T1 VALUES ('张三','A')
INSERT INTO #T1 VALUES ('张三','C')
INSERT INTO #T1 VALUES ('张三','E')
INSERT INTO #T1 VALUES ('李四','A')
INSERT INTO #T1 VALUES ('李四','B')
INSERT INTO #T1 VALUES ('李四','C')
CREATE TABLE #T2
(
  TYPE VARCHAR(10)
)
INSERT INTO #T2 VALUES ('A')
INSERT INTO #T2 VALUES ('B')
INSERT INTO #T2 VALUES ('C')
INSERT INTO #T2 VALUES ('D')
INSERT INTO #T2 VALUES ('E')

SELECT distinct OutT1.NAME,#T2.TYPE as Type2 FROM #T1 OutT1 cross JOIN #T2
where #T2.TYPE not in (select innerT1.TYPE from  #T1 innerT1 where OutT1.NAME=innerT1.NAME)

[专注or全面]

[sqlkxr]

create table test1(a nvarchar(10),b_type varchar(10)) create table test11(b_type varchar(10)) insert into test1 select N'张三','A01' insert into test1 select N'张三','A02' insert into test1 select N'张三','A05' insert into test1 select N'李四','A01' insert into test1 select N'李四','A03' insert into test1 select N'李四','A05' insert test11 select 'A01' insert test11 select 'A02' insert test11 select 'A03' insert test11 select 'A04' insert test11 select 'A05' ---竟然又用到游标了，想不出来用语句怎么写 declare cur_t cursor for select distinct a from test1 if object_id('t') is not null drop table t select * into t from test1 where 1<>1 declare @a varchar(10) open cur_t fetch next from cur_t into @a while @@fetch_status=0 begin insert into t select @a,a.b_type from test11 a where not exists (select * from test1 where b_type=a.b_type and a=@a) fetch next from cur_t into @a end select * from t close cur_T deallocate cur_t

[發糞塗牆]

SELECT  *
 FROM    a
 WHERE   NOT EXISTS ( SELECT 1
                      FROM   b
                      WHERE  a.b_type = b.b_type )

[dljinsui]

好像不对哦。