如何在具有复合键列表的表上执行 SELECT？

Question

如果我有一个包含类似列的表

ID1 ID2 VAL1 VAL2

其中ID1,ID2为主键索引。

如何针对特定 ID1、ID2 值执行 SQL 语句？

示例：我有一个包含 PK (1,1) (1,2) (2,4) (2,1) (3,1) (3,2) (3,5) 记录的表，

我只想选择包含 (1,1) (1,2) (3,5) (3,2) 的记录

SELECT 查询

从 tbl1 中选择 *，其中 ID1 IN (1,3) AND ID2 IN (1,2,5)

将产生不需要的结果：(3,1)。那么最好的方法是什么？

我正在寻找 SQL 的整体答案，但如果它依赖于 DBMS，我想知道如何在 PostgreSQL 和 MySQL 中这样做。

Answer 1

MySQL 和 PostgreSQL 都支持 IN 中的元组，如下所示。

SELECT * 
FROM tbl1
WHERE (ID1,ID2) IN ((1,1) (1,2) (2,4) (2,1) (3,1) (3,2) (3,5));

Answer 2

您可以使用 and 和 or 来做到这一点：

SELECT  * 
FROM    tbl1
WHERE   ID1 = 1 AND ID2 = 1 OR
        ID1 = 1 AND ID2 = 2 OR
        ID1 = 3 AND ID2 = 5 OR
        ID1 = 2 AND ID2 = 2

Answer 3

这可能是执行您请求的最紧凑的方式：

SELECT *
FROM tbl1
WHERE (ID1 = 1 AND (ID2 = 1 OR ID2 = 2))
   OR (ID1 = 3 AND (ID2 = 5 OR ID2 = 2));

Answer 4

SELECT * FROM tbl1 WHERE ID1 = 1 AND ID2 IN (1,2)
UNION
SELECT * FROM tbl1 WHERE ID1 = 3 AND ID2 IN (5,2) 

或者

SELECT * FROM tbl1 WHERE (ID1 = 1 AND ID2 IN (1,2)) OR (ID1 = 3 AND ID2 IN (5,2)) 

Answer 5

你也可以尝试

SELECT
   *
FROM
  tbl1
  JOIN
  (
    SELECT 1 AS ID1, 1 AS ID2
    UNION ALL
    SELECT 1 AS ID1, 2 AS ID2
    UNION ALL
    SELECT 2 AS ID1, 4 AS ID2
    UNION ALL
    SELECT 2 AS ID1, 1 AS ID2
    UNION ALL
    SELECT 3 AS ID1, 1 AS ID2
    UNION ALL
    SELECT 3 AS ID1, 2 AS ID2
    UNION ALL
    SELECT 3 AS ID1, 5 AS ID2
  ) as keylist ON tbl1.ID1 = keylist.ID1 AND tbl1.ID2 = keylist.ID2