mysql - SQL - 选择与 3 个组合条件中的几个相匹配的值

Question

在尝试解决这个问题并在网上寻找答案失败后，我只能寻求帮助 - 所以任何人都将不胜感激!

我有一个具有以下结构的 SQL 表:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(25)     | NO   | PRI | NULL    | auto_increment |
| municipality | varchar(50) | NO   | MUL | NULL    |                |
| admin        | varchar(50) | NO   |     | NULL    |                |
| country      | varchar(50) | NO   |     | NULL    |                |
| longitude    | varchar(50) | NO   |     | NULL    |                |
| latitude     | varchar(50) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

并且我希望选择此表的一个子集，其中满足多个三元组条件，例如:市镇 = '纽约'，admin = '纽约'，国家 = '美国'。我希望这样做，并且不仅选择市，因为有时不同的行政区域存在相同的地名。

这是我迄今为止尝试过的:

SELECT * FROM locations WHERE (municipality, admin, country) IN 
('Pearl River', 'New York', 'United States'), 
('Shelton', 'Washington', 'United States'), 
('Granite', 'Illinois', 'United States'), 
('Washington', 'Pennsylvania', 'United States'), 
('Erlanger', 'Kentucky', 'United States'), 
('Warren', 'Ohio', 'United States');

以及:

SELECT * FROM locations WHERE municipality IN ('Pearl River', 'Shelton', 'Granite', 'Washington', 'Erlanger', 'Warren') 
AND WHERE admin IN ('New York', 'Washington', 'Illinois', 'Pennsylvania', 'Kentucky', 'Ohio') 
AND WHERE country IN ('United States', 'United States', 'United States', 'United States', 'United States', 'United States');

但是两个查询都只返回此标准错误:无法运行语句:您的 SQL 语法中有错误；

我真的希望有人能帮助我，提前非常感谢!

Answer 1

您应该用括号将要匹配的元组列表括起来，并在它们前面加上“VALUES”前缀。例如，

SELECT * FROM locations WHERE (municipality, admin, country) IN (
    VALUES 
        ('Pearl River', 'New York', 'United States'), 
        ('Pearl', 'New York', 'United States'), 
        ('Athens', 'Georgia', 'United States')
);

我已经针对具有以下结构的 sqlite 数据库对此进行了测试，并得到了正确的输出，我认为。

CREATE TABLE locations (municipality TEXT, admin TEXT, country TEXT);
INSERT INTO locations VALUES('Pearl River','New York','United States');
INSERT INTO locations VALUES('Pearl River','Alabama','United States');
INSERT INTO locations VALUES('Athens','Georgia','United States');

返回以下内容:

Pearl River|New York|United States
Athens|Georgia|United States

-- 忍者编辑 --

请注意，上述查询在 SQLite 中有效，但在 MySQL 中无效。要使此查询适用于 MySQL，只需删除“VALUES”关键字，但保留元组列表两边的括号至关重要。例如，

SELECT * FROM locations WHERE (municipality, admin, country) IN (
        ('Pearl River', 'New York', 'United States'), 
        ('Pearl', 'New York', 'United States'), 
        ('Athens', 'Georgia', 'United States')
);