MySQL :如何在组合主键上使用"IN“过滤查询

Question

我在MySQL中有一个表，这样主键就是一个复合键，如下所示：

+--------------+----+------------------------------+
| fk_something | id |            label             |
+--------------+----+------------------------------+
|            1 |  1 | First record of something 1  |
|            2 |  1 | First record of something 2  |
|            2 |  2 | Second record of something 2 |
|            1 |  2 | Second record of something 1 |
|            2 |  3 | Third record of something 2  |
+--------------+----+------------------------------+

但现在我有一个棘手的查询要做，例如，我想使用"IN“过滤器检索2-1和1-2记录……我该怎么做呢？

我试过这样的方法：

SELECT * FROM my_table WHERE id IN (1, 2) AND fk_something IN (2, 1)

但它也给了我1-1和2-2的记录...

Answer 1

您可以利用很少使用的名为row value expressions的SQL特性。

SELECT * FROM myTable
WHERE (id, fk_something) IN (
(1, 2),
(2, 1),
(2, 3)
)

Answer 2

我想这对你有帮助

SELECT *
FROM my_table 
WHERE id IN (1, 2) 
AND fk_something IN (1, 2)
AND id != fk_something