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

找出不同的记录

hongyuan20022003 2007-12-14 05:43:13

表格如下：
s e aa bb cc
0 1 3 4 6
2 4 6 6 4
............

s\e\aa\bb\cc均为正整数，

有两个这样的表格，hh和gg

select distinct s,e,aa,bb,cc from hh
记录数为108
select distinct s,e,aa,bb,cc from gg
记录数为107

select distinct s,e,aa,bb from hh
记录数为36
select distinct s,e,aa,bb from gg
记录数为36

怎样找出表格hh中独有（gg中没有）的记录？

...全文

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

Moose 2007-12-15

select hh.*
from hh
left join gg
on gg.cc=hh.cc
and gg.s=hh.s
and gg.e=hh.e
and gg.aa=hh.aa
and gg.bb=hh.bb
where gg.cc is null

回复赞

中国风 2007-12-14

select distinct s,e,aa,bb,cc from hh where checksum(s,e,aa,bb,cc) not int (select checksum(s,e,aa,bb,cc) from gg)



select distinct s,e,aa,bb from hh where checksum(s,e,aa,bb) not in(select checksum(s,e,aa,bb) from gg)



或



select 

	distinct s,e,aa,bb,cc 

from 

	hh 

where

	 not exists (select 1 from gg where s=hh.s and e=hh.e and  aa=hh.aa and bb=hh.bb and cc=hh.cc)



select 

	distinct s,e,aa,bb,cc 

from 

	hh 

where

	 not exists (select 1 from gg where s=hh.s and e=hh.e and  aa=hh.aa and bb=hh.bb)

回复赞

zheninchangjiang 2007-12-14

select distinct s,e,aa,bb from hh
记录数为36
select distinct s,e,aa,bb from gg
记录数为36
这个并不能证明前面4列的相同性，所以还得：
select * from hh a where not exists(select 1 from gg where s=a.s and e=a.e and aa=a.aa and bb=a.bb and cc=a.cc)

回复赞

中国风 2007-12-14

select * from hh where checksum(s,e,aa,bb,cc) not in(select checksum(s,e,aa,bb,cc) from gg)



select * from hh where checksum(s,e,aa,bb) not in(select checksum(s,e,aa,bb) from gg)

回复赞

hongyuan20022003 2007-12-14

cc的范围是-1/0/1

回复赞

Moose 2007-12-14

select hh.*
from hh
left join gg on gg.cc = hh.cc
where gg.cc is null

回复赞

发动态

发帖子

MS-SQL Server

创建于2007-09-28

加入

3.2w+

社区成员

25.3w+

社区内容

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

申请成为版主

社区公告

暂无公告