多列子选择等效于sql server 2005 [英] multi column sub select equivalent for sql server 2005

问题描述

谁能告诉我如何在sql server中执行此操作？

我目前正在oracle中执行此查询：


select table1.col1，table1 .col2，table2.col3，table4.col4

其中table1.col1 = table2.col3和

table2.col3 = table4.col5和

（table1.col1，table1.col2）不在

select table2.col4，table2col5 from table2

它是来自任何一行的两列值不是在table2中的任何

行中发现我无法弄清楚。


谢谢

Jeff

解决方案

Jeff Kish（ki*******@charter.net）写道：

谁能告诉我如何在sql server中执行此操作？

我目前正在oracle中执行此查询：


select table1.col1，table1.col2 ，table2.col3，table4.col4

其中table1.col1 = table2.col3和

table2.col3 = table4.col5和

（ table1.col1，table1.col2）不在

从table2中选择table2.col4，table2.col5


这是任何行中没有找到任何行的两列值的地方
$ b表2中的$ b行是我无法弄清楚的。

行构造函数是ANSI Stanard的一部分，但未在

SQL Server中实现。所以只有在那里为甲骨文填写一个。


幸运的是，在这种情况下你也可以使用NOT EXISTS：


选择...

来自table1 t1

哪里不存在（选择*

from table2 t2

其中t1.col1 = t2.col1

和t1.col2 = t2.col2）


-

Erland Sommarskog， SQL Server MVP， es****@sommarskog.se


联机丛书SQL Server 2005
http： //www.microsoft.com/technet/pro...ads/books.mspx

SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx

12月4日上午9:15，Jeff Kish< kishjj ... @ chart er.netwrote：

有人能告诉我如何在sql server中执行此操作吗？

我目前正在oracle中执行此查询：


选择table1.col1，table1.col2，table2.col3，table4.col4

其中table1.col1 = table2.col3和

table2.col3 = table4.col5和

（table1.col1，table1.col2）不在

select table2.col4，table2.col5 from table2


在table2部分的任何

行中找不到任何行的两列值，我无法弄明白。


谢谢

杰夫

它可以采用略有不同的方式，

select table1.col1，table1.col2，table2.col3，table4.col4

其中table1.col1 = table2.col3和

table2.col3 = table4.col5和

cast（table1 as varchar（20））+'' - ''+ cast（col1 as varchar（20））not in（

select cast（table2.col4 as varchar （20））+'' - ''+演员（表2 .col5 as

varchar（20））来自table2）


问候

Monojit

< br>

2007年12月4日星期二09:25:48 +0000（UTC），Erland Sommarskog

< es **** @ sommarskog.sewrote：
< blockquote class =post_quotes>
> Jeff Kish（ki*******@charter.net）写道：

>可以谁有人告诉我如何在sql server中执行此操作？
我目前正在oracle中执行此查询：

select table1.col1，table1.col2，table2.col3，table4.col4
其中table1.col1 = table2.col3和
table2.col3 = table4.col5和
（table1.col1，table1.col2）不在
select table2.col4，table2.col5来自table2

在table2部分的任何
行中找不到任何行的两列值，我无法弄清楚。

行构造函数是ANSI Stanard的一部分，但未在SQL Server中实现。因此，只有在那里为Oracle填写一个。

幸运的是，在这种情况下你也可以使用NOT EXISTS：


select。 ..

来自table1 t1

哪里不存在（从表2中选择*

t2

其中t1.col1 = t2.col1

和t1.col2 = t2.col2）

非常感谢。我会测试一下。

Jeff

Can anyone tell me how to do this in sql server?
I am currently doing this query in oracle:

select table1.col1,table1.col2,table2.col3,table4.col4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,table1.col2) not in
select table2.col4,table2.col5 from table2
it is the where two column values from any row are not found in any
row in table2 part that I can''t figure out.

thanks
Jeff

解决方案

Jeff Kish (ki*******@charter.net) writes:

Can anyone tell me how to do this in sql server?
I am currently doing this query in oracle:

select table1.col1,table1.col2,table2.col3,table4.col4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,table1.col2) not in
select table2.col4,table2.col5 from table2
it is the where two column values from any row are not found in any
row in table2 part that I can''t figure out.

Row constructors is part of the ANSI Stanard, but not implemented in
SQL Server. So it''s only to chalk one up for Oracle there.

Fortunately, in this situation you could just as well use NOT EXISTS:

select ...
from table1 t1
where not exists (select *
from table2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Dec 4, 9:15 am, Jeff Kish <[e-mail]:

Can anyone tell me how to do this in sql server?
I am currently doing this query in oracle:

select table1.col1,table1.col2,table2.col3,table4.col4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,table1.col2) not in
select table2.col4,table2.col5 from table2

it is the where two column values from any row are not found in any
row in table2 part that I can''t figure out.

thanks
Jeff

It can be done slightly different way,
select table1.col1,table1.col2,table2.col3,table4.col4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
cast(table1 as varchar(20)) +''-''+cast(col1 as varchar(20)) not in(
select cast(table2.col4 as varchar(20)) + ''-'' + cast(table2.col5 as
varchar(20)) from table2)

Regards
Monojit

On Tue, 4 Dec 2007 09:25:48 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.sewrote:

>Jeff Kish (ki*******@charter.net) writes:

>Can anyone tell me how to do this in sql server?
I am currently doing this query in oracle:

select table1.col1,table1.col2,table2.col3,table4.col4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,table1.col2) not in
select table2.col4,table2.col5 from table2
it is the where two column values from any row are not found in any
row in table2 part that I can''t figure out.

Row constructors is part of the ANSI Stanard, but not implemented in
SQL Server. So it''s only to chalk one up for Oracle there.

Fortunately, in this situation you could just as well use NOT EXISTS:

select ...
from table1 t1
where not exists (select *
from table2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)

thanks much. I''ll test things out.
Jeff

这篇关于多列子选择等效于sql server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！