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

问题描述

我有一张名为 player 的桌子，如下所示:

I have a table called players as follows:

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    Name
1           1            Durant
1           2            Lebron
1           3            Dirk

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

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

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))

此语法不适用于 Mainframe 上的 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屋！