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

找出不同的记录

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中没有)的记录?


...全文
55 点赞 收藏 6
写回复
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+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告