MySQL "ANY IN“查询？

Question

在MySQL中有没有这样做的捷径：

WHERE s.loc1 IN ("A", "B", "C", "D") 
   OR s.loc2 IN ("A", "B", "C", "D") 
   OR s.loc3 IN ("A", "B", "C", "D");

而不重复("A"，"B"，"C"，"D")位(最好是这个列表仍然可以通过@param提供)？不幸的是，(s.loc1，s.loc2，s.loc3) IN (诸如此类)的语法似乎只适用于AND而不是OR。

理想的解决方案应该类似于：

WHERE (s.loc1, s.loc2, s.loc3) ANY IN ("A", "B", "C", "D");

Answer 1

您可以从由值的联合生成的表(即，表子查询或derived table)中进行查询：

select
    *
from (
    (select loc1 as loc, s.* from s)
    union
    (select loc2 as loc, s.* from s)
    union
    (select loc3 as loc, s.* from s)
) t
where
    t.loc in ("A","B","C","D");

运行上述查询会产生以下结果：

+-----+------+------+------+
| loc | loc1 | loc2 | loc3 |
+-----+------+------+------+
| A   | A    | 2    | 3    |
| D   | D    | 2    | 3    |
| B   | 1    | B    | 3    |
| C   | 1    | 2    | C    |
+-----+------+------+------+

在此虚构的数据上运行时：

drop table if exists s;
create table s (
    loc1 varchar(255) not null,
    loc2 varchar(255) not null,
    loc3 varchar(255) not null
);
insert into s values
("1","2","3"),
("A","2","3"),
("1","B","3"),
("1","2","C"),
("D","2","3");

另请参阅demo version