mysql - 两列的 WHERE + AND 的 SQL 多重查询

Question

我有一个订单数据库，大致如下所示

   ORDER ID | AMOUNT | FIRSTNAME | LASTNAME
     1         50        JOHN       SMITH
     2         60        PAUL       JOHNSON
     3         20        FRANK      CAMERON
     5         80        JOHN       JOHNSON

我有一个名单，我需要拉他们的订单。对于这个例子，我们说“JOHN SMITH”和“PAUL JOHNSON”

我试过这样的查询

SELECT * FROM ORDERS WHERE FIRSTNAME IN ("JOHN", "PAUL") AND LASTNAME IN ("SMITH", "JOHNSON")

但显然这并没有像我想要的那样工作。我不确定该怎么做，但我需要搜索类似的内容

WHERE FIRSTNAME,LASTNAME IN ("JOHN","SMITH";"PAUL","JOHNSON")

或

WHERE (FIRSTNAME,LASTNAME) IN {("JOHN","SMITH")("PAUL","JOHNSON")}

显然，我对 SQL 知之甚少，所以我对要搜索的内容的语法和术语感到困惑？

Answer 1

您可以像这样用 OR 分隔逻辑:

SELECT * FROM ORDERS
WHERE 
(FIRSTNAME = "JOHN" AND LASTNAME = "SMITH")
OR
(FIRSTNAME = "PAUL" AND LASTNAME = "JOHNSON")