T-SQL equivalent to oracle sql where clause with multiple columns

Let's look for example Oracle SQL, which works perfectly:

让我们来看看Oracle SQL,它完美地运行:

Sample data:

SQL> create table test (a number, b number);
SQL> insert into test values(1, 1);
SQL> insert into test values(1, 2);
SQL> insert into test values(1, 3);
SQL> insert into test values(1, 4);
SQL> insert into test values(1, 5);
SQL> insert into test values(2, 1);
SQL> insert into test values(2, 2);
SQL> insert into test values(2, 3);
SQL> insert into test values(2, 4);
SQL> insert into test values(2, 5);
SQL> insert into test values(4, 1);

SQL> select * from test;

         A          B
---------- ----------
         1          1
         1          2
         1          3
         1          4
         1          5
         2          1
         2          2
         2          3
         2          4
         2          5
         4          1

Query:

SQL> select * from test where (a, b) in (select 1, 4 from dual);

         A          B
---------- ----------
         1          4

Here's the sql-fiddle: http://www.sqlfiddle.com/#!4/8375e/3/0

这是sql-fiddle:http://www.sqlfiddle.com/#!4/8375e/3/0

Simple question: is there any equivalent in MS SQL of above "where (a, b)" clause? I've been looking around in google, MS Docs and nothing so far...

简单问题:上面“where(a,b)”子句的MS SQL中是否有任何等价物?我一直在寻找谷歌,MS Docs,到目前为止没什么...

2 个解决方案

#1

While SQL Server has a Table Value Constructor that can be used for some use-cases, SQL Server doesn't support SQL standard row value expressions and predicates derived from row value expressions for general use (yet). You will have to resort to semi-joining your subquery using an equivalent EXISTS clause:

虽然SQL Server具有可用于某些用例的表值构造函数,但SQL Server不支持SQL标准行值表达式和从行值表达式派生的谓词(尚未使用)。您将不得不使用等效的EXISTS子句来使用半连接子查询:

This:

select * from test where (a, b) in (select 1, 4 from dual);

Is equivalent to this (see SQLFiddle demo):

相当于此(参见SQLFiddle演示):

select * from test where exists (
  select * from (
    select 1, 4 -- Replace with "real" subselect
  ) t(a, b)
  where test.a = t.a and test.b = t.b
)

Or, a bit more generically, by using a common table expression (See SQLFiddle demo):

或者,更一般地说,通过使用公用表表达式(请参阅SQLFiddle演示):

with t(a, b) as (
  select 1, 4 -- Replace with "real" subselect
)
select * from test where exists (
  select * from t
  where test.a = t.a and test.b = t.b
)

#2

How about below query, which supports in sql server; and I guess a=1 and b=4 gives the same result in sql server equivalent to oracle query.:

下面的查询怎么样,在sql server中支持;我猜a = 1和b = 4在sql server中给出了相同的结果,相当于oracle查询:

select 
    * 
from 
    test 
where 
    a=1 and 
    b=4;

#1