使用多个in语句但不使用笛卡尔积的查询

SELECT  *
FROM    mytable
WHERE   (a, b, c) IN ((1, 4, 7), (2, 5, 8), (3, 6, 9))

,或更方便索引的解决方案:

SELECT  *
FROM    mytable
WHERE   (a, b, c)  = (1, 4, 7)
        OR (a, b, c)  = (2, 5, 8)
        OR (a, b, c)  = (3, 6, 9)

不幸的是, MySQL 中的记录类型不使用索引。 IN 条款。

在MSSQL中,可以执行以下操作:

SELECT * FROM Table t 
join (
        select 1 a, 2 b, 3 c
  union select 4 a, 5 b, 6 c
  union select 7 a, 8 b, 9 c
) x on t.a=x.a and t.b=x.b and t.c=x.c

我不确定这是否会直接翻译成mysql。

当然,更简单的方法是:

SELECT * FROM Table t 
WHERE (t.a=1 and t.b=4 and t.c=7)
   or (t.a=2 and t.b=5 and t.c=8)
   or (t.a=3 and t.b=6 and t.c=9)

select t1.a, t2.b, t3.c
from Table t1
inner join Table t2 on (t1.a = 1 and t2.b = 4) or (t1.a = 2 and t2.b = 5) or (t1.a = 3 and t2.b = 6)  
inner join Table t3 on (t2.b = 4 and t3.c = 7) or (t2.b = 5 and t3.c = 8) or (t2.b = 6 and t3.c = 9)