有两个表:T1,T2
T1: A B
A1 B1 A1 B2 A1 B3 A2 B1 A2 B2 A2 B4 A3 B3 A3 B1 A4 B2 A4 B3 A4 B6 A5 B1将只A列中B列只带B1,B2,B4的数据查出来
结果应该是:
A1和A5
应该怎么写
T1: A B
A1 B1 A1 B2 A1 B3 A2 B1 A2 B2 A2 B4 A3 B3 A3 B1 A4 B2 A4 B3 A4 B6 A5 B1将只A列中B列只带B1,B2,B4的数据查出来
结果应该是:
A1和A5
应该怎么写
select distinct A from tb where b not in ('B1','B2','B3'))
--这样吗?select * from tb where a not in (
select distinct A from tb where b not in ('B1','B2','B4'))
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(2),[B] varchar(2))
insert [tb]
select 'A1','B1' union all
select 'A1','B2' union all
select 'A1','B3' union all
select 'A2','B1' union all
select 'A2','B2' union all
select 'A2','B4' union all
select 'A3','B3' union all
select 'A3','B1' union all
select 'A4','B2' union all
select 'A4','B3' union all
select 'A4','B6' union all
select 'A5','B1'
go-->测试开始
select * from [tb] t where not exists
(
select 1 from [tb] where a=t.a and b not in ('B1','B2','B4')
)
/*
A B
---- ----
A2 B1
A2 B2
A2 B4
A5 B1(4 行受影响)*/应该是a2 a5吧