主页
基础类
应用实例
新技术前沿

求一条简单的sql

yhyhai 2008-01-18 02:30:25

有表X(a1,a2)
a1 a2
----------
01 a
01 b
02 a
02 b
03 a

有表Y(a1,a2)
a1 a2
----------
01 a
01 b
02 a
02 b
03 a
03 b

请问如何通过sql来得到如下结果集?谢谢!
a1 a2
----------
03 b

...全文

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

huzhenqi2008 2008-01-18

select * from Y
where not exists(select * from X,Y where X.a1=Y.a1 and X.a2=Y.a2)

看到红色部分没有?,要联表查询~~~~

回复赞

JL99000 2008-01-18

select * from Y where not exists (select 1 from X where X.a1=Y.a1 and X.a2=Y.a2)

回复赞

TNT_1st_excellence 2008-01-18

SELECT a1, a2 FROM b
EXCEPT
SELECT a1, a2FROM a

回复赞

yangjiexi 2008-01-18



select * from Y 

where not exists(select 1 from X where X.a1=Y.a1 and X.a2=Y.a2)

回复赞

wzy_love_sly 2008-01-18

declare @a table (a1 varchar(10),a2 varchar(10))

insert into @a select '01','a'

insert into @a select '01','b'

insert into @a select '02','a'

insert into @a select '02','b'

insert into @a select '03','a'

declare @b table (a1 varchar(10),a2 varchar(10))

insert into @b select '01','a'

insert into @b select '01','b'

insert into @b select '02','a'

insert into @b select '02','b'

insert into @b select '03','a'

insert into @b select '03','b'



select * from @b y where 

not exists(select 1 from @a where a1=Y.a1 and a2=Y.a2)

a1 a2
03 b

怎么会没有用啊!

回复赞

dawugui 2008-01-18

算法?
规则?

没有咋搞?

回复赞

yhyhai 2008-01-18

to libin_ftsafe
你的sql没有用啊!

回复赞

miaoyuanyan 2008-01-18

应该用到右外部连接

回复赞

wzy_love_sly 2008-01-18

哈哈，多给点分

回复赞

子陌红尘 2008-01-18

select * from Y where not exists(select 1 from X where a1=Y.a1 and a2=Y.a2)

回复赞

shirley_yue 2008-01-18

规则?

回复赞

发动态

发帖子

MS-SQL Server

创建于2007-09-28

加入

3.2w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区

申请成为版主

社区公告

暂无公告