in”查询？

vybvopom 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(246)

在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仍然支持此列表）？遗憾的是，（blah）中（s.loc1，s.loc2，s.loc3）的语法似乎只适用于and's而不是or's。
理想的解决方案如下：

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

mysql sql-in Any

来源：https://stackoverflow.com/questions/52121085/mysql-any-in-query

1条答案

按热度按时间

2cmtqfgy1#

您可以从以下值的并集生成的表（即表子查询或派生表）中进行查询：

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

另请参见演示版本

赞(0）回复(0）举报 2021-06-20

我来回答

in”查询？

1条答案

相关问题

热门标签

最新问答