使用一个in子句选择一对字段查询 [英] Select Query by Pair of fields using an in clause

问题描述

我有一个名为玩家的表格如下：

I have a table called players as follows:

First_Id        Second_Id      ;  名称
1                 1                      杜兰特
2   ;               1            ;                                  2                       Lebron

2                 2&n bsp;                     Dwight

1                 3          ;             Dirk

First_Id       Second_Id       Name
1                1                     Durant
2                1                     Kobe
1                2                     Lebron
2                2                     Dwight
1                3                     Dirk

我想写一个这个表上的select语句可以检索其第一个id和第二个id匹配一组指定的第一个和第二个id的所有行。

I wish to write a select statement on this table to retrieve all rows whose first ids and second ids match a bunch of specified first and second ids.

所以例如，我希望选择所有行其第一和第二个ID如下：（1,1），（1,2）和（1,3）。这将会检索以下3行：

So for example, I wish to select all rows whose first and second ids are as follows: (1,1), (1,2) and (1,3). This would retreive the following 3 rows:

First_Id        Second_Id        ;名称
1                 1                       Durant

1     ;             2              ;                          3                      Dirk号

First_Id       Second_Id       Name
1                1                     Durant
1                2                     Lebron
1                3                     Dirk

是否可以以如下方式编写选择查询：

Is it possible to write a select query in a manner such as:

SELECT * FRO M PLAYERS
WHERE（First_Id，Second_Id）IN（（1,1），（1,2）和（1,3））？

SELECT * FROM PLAYERS WHERE (First_Id, Second_Id) IN ((1,1), (1,2) and (1,3))?

如果有是写一个类似于上面我想知道的SQL的一种方法。有没有办法为IN子句指定代表多个行的值，如图所示。

If there is a way to write the SQL similar to the above I would like to know. Is there a way to specify values for an IN clause that represents multiple rows as illustrated.

我正在使用DB2。

推荐答案

这可以使用以下语法在我的DB2（Linux / Unix / Windows上的9.7版本）上运行：

This works on my DB2 (version 9.7 on Linux/Unix/Windows) by using this syntax:

SELECT *
FROM PLAYERS
WHERE (First_Id, Second_Id) IN (VALUES (1,1), (1,2), (1,3))

此语法在主机上的DB2（至少在版本9.1）中将无法使用，因为您无法替代一个带有VALUES表达式的子选择。这个语法将会起作用：

This syntax won't work on DB2 on the Mainframe (at least in version 9.1) because you can't substitute a sub-select with a VALUES expression. This syntax will work:

SELECT *
FROM PLAYERS
WHERE (First_Id, Second_Id) IN (SELECT 1, 1 FROM SYSIBM.SYSDUMMY1 UNION ALL
                                SELECT 1, 2 FROM SYSIBM.SYSDUMMY1 UNION ALL
                                SELECT 1, 3 FROM SYSIBM.SYSDUMMY1)

这篇关于使用一个in子句选择一对字段查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！