求一句SQL语句。（急） - 编程论坛

棉花糖ONE2007-03-12 11:03

create table shiyan(col1 char(3),col2 int)
insert into shiyan select 'AAA',111
union select 'AAA',222
union select 'AAA',333
union select 'BBB',111
union select 'BBB',222
union select 'CCC',111
union select 'CCC',222
union select 'DDD',111
union select 'DDD',222
union select 'DDD',333

select distinct col1 from shiyan s1 where not exists(select 1 from shiyan s2 where s1.col1=s2.col1 and s2.col2=333)

select distinct col1 from shiyan s1 where col1 not in (select col1 from shiyan where col2=333)

power6d2007-03-12 11:04

已经解决了。呵呵，脑子一下子没转过来。

以下是答案：
Declare @Tmp1 Table
(
Field1 Char(5),
Field2 Char(5)
)

Insert Into @Tmp1
Select 'AAA','111' Union
Select 'AAA','222' Union
Select 'AAA','333' Union
Select 'BBB','111' Union
Select 'BBB','222' Union
Select 'CCC','111' Union
Select 'CCC','222' Union
Select 'DDD','111' Union
Select 'DDD','222' Union
Select 'DDD','333'

Select Distinct Field1 From @Tmp1 A Where Not Exists(Select * From @Tmp1 B Where Field2='333' And A.Field1=B.Field1)