ITPub博客

首页 > Linux操作系统 > Linux操作系统 > TSQL中实现ORACLE的多列IN

TSQL中实现ORACLE的多列IN

原创 Linux操作系统 作者:pgstar 时间:2009-02-17 10:05:59 0 删除 编辑
期望效果:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->select * From Empoylee Where
(Address1,Address2) 
in
(
Select Address1,Address2
From EmpoyleeAdresses
Where Country = 'Canada')
以上无法实现
解决方案1:用exists判断
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->select a.* From Empoylee a
Where exists
    (
select 'A' from EmpoyleeAdresses b
     
Where
        b.Country 
= 'Canada'
            
and a.Address1=b.Address1
            
and a.Address2=b.Address2
        )
解决方案2:用inner join内链接
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->select *
From
Empoylee
inner join EmpoyleeAdresses
on
Empoylee.Address1
=EmpoyleeAdresses.Address1
and
Empoylee.Address2
=EmpoyleeAdresses.Address2
Where
EmpoyleeAdresses.Country 
= 'Canada')
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->select * 
from
Empoylee em
inner join EmpoyleeAdresses emA
on
em.Address1
=emA.Address1
and
em.Address2
=emA.Address2
and
emA.Country 
= 'Canada'
解决方案3:表关联
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->select a.*
From 
Empoylee a ,
  (
Select
  Address1,Address2 
From EmpoyleeAdresses 
  
Where
  Country 
= 'Canada') b
Where
a.Address1
=b.Address1
and
a.Address2 
= b.Address2

主观感觉方案1更快一些,客观上,还没在查询分析器里用"显示执行计划"看哪种写法效率高些.

如果是Delete语句,写法如下:

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->Delete From Empoylee 
Where exists
    (
select 'A' from EmpoyleeAdresses b
     
Where
        b.Country 
= 'Canada'
            
and Empoylee.Address1=b.Address1
            
and Empoylee.Address2=b.Address2
        )

注意Delete from 后的表不能用别名~

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/216599/viewspace-553502/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-11-07

  • 博文量
    2
  • 访问量
    4408