MYSQL：你能拉出与4个表达式中的3个匹配的结果吗？

Say I have a query like this:

说我有这样的查询：

SELECT * FROM my_table WHERE name = "john doe" AND phone = "8183321234" AND email = "johndoe@yahoo.com" AND address = "330 some lane";

But say I only need 3 out of the 4 to match, I know I can write a very long query with several ORs but I was wondering if there was a feature for this?

但是说我只需要4个中的3个来匹配，我知道我可以用几个OR写一个很长的查询，但我想知道是否有这个功能？

Thanks.

谢谢。

5 个解决方案

#1

SELECT
  * 
FROM 
  my_table 
WHERE 
  CASE WHEN name = "john doe"           THEN 1 ELSE 0 END +
  CASE WHEN phone = "8183321234"        THEN 1 ELSE 0 END +
  CASE WHEN email = "johndoe@yahoo.com" THEN 1 ELSE 0 END +
  CASE WHEN address = "330 some lane"   THEN 1 ELSE 0 END
  >= 3;

Side note: this will very likely not be using indexes efficiently. On the other hand, there will very likely be no indexes on these kinds of columns anyway.

旁注：这很可能不会有效地使用索引。另一方面，无论如何，很可能在这些类型的列上没有索引。

#2

Holy overcomplexity, Batman.

神圣的过于复杂，蝙蝠侠。

SELECT * 
FROM my_table 
WHERE (
    (name = "john doe") +
    (phone = "8183321234") +
    (email = "johndoe@yahoo.com") +
    (address = "330 some lane")
) >= 3;

#3

Same thing using indexes:

使用索引同样的事情：

SELECT  *
FROM    (
        SELECT  id
        FROM    (
                SELECT  id
                FROM    mytable _name
                WHERE   name = 'john doe'
                UNION ALL
                SELECT  id
                FROM    mytable _name
                WHERE   phone = '8183321234'
                UNION ALL
                SELECT  id
                FROM    mytable _name
                WHERE   email = "johndoe@yahoo.com"
                UNION ALL
                SELECT  id
                FROM    mytable _name
                WHERE   address = '330 some lane'
                ) q
        GROUP BY 
                id
        HAVING
                COUNT(*) >= 3
        ) di, mytable t
WHERE   t.id = di.id

See the entry in my blog for performance details.

有关性能详细信息，请参阅我博客中的条目。

#4

I like the IF construct:

我喜欢IF结构：

SELECT * FROM my_table
WHERE
(    IF(name    = 'john doe', 1, 0) +
     IF(phone   = '8183311234', 1, 0) +
     IF(email   = 'johndoe@yahoo.com', 1, 0) +
     IF(address = '330 some lane', 1, 0)
) >= 3

#5

Modifying Tomalak's query slightly so that it will use indexes if they are present. Although unless there is an index on each field, a full table scan will happen anyway.

稍微修改Tomalak的查询，以便在它们存在时使用索引。虽然除非每个字段都有索引，否则无论如何都会进行全表扫描。

SELECT
*, 
(
    IF(name="john doe", 1, 0) +
    IF(phone = "8183321234", 1, 0) +
    IF(email = "johndoe@yahoo.com", 1, 0) +
    IF(address = "330 some lane", 1, 0) 
) as matchCount
FROM my_table 
WHERE 
    name = "john doe" OR 
    phone = "8183321234" OR 
    email = "johndoe@yahoo.com" OR 
    address = "330 some lane"
HAVING matchCount >= 3

#1

SELECT
  * 
FROM 
  my_table 
WHERE 
  CASE WHEN name = "john doe"           THEN 1 ELSE 0 END +
  CASE WHEN phone = "8183321234"        THEN 1 ELSE 0 END +
  CASE WHEN email = "johndoe@yahoo.com" THEN 1 ELSE 0 END +
  CASE WHEN address = "330 some lane"   THEN 1 ELSE 0 END
  >= 3;