mysql - SQL 将列的值与其他列的所有值进行匹配(除了值)

Question

我只有一张表:产品

ID | Product Name | Code 1 | Code 2 | Code 3
********************************************
1  |    Phone1    |   MM   |   DD   |   nd
2  |    Phone2    |   DD   |        |   nd
3  |    Phone3    |   MM   |   ZZ   |   nd
4  |    Phone4    |   XX   |        |   nd

我愿意:

select all phones which one of their codes match one of the Phone1's code BUT NOT IF THE CODE = ND

所以它应该返回Phone2和Phone3，但现在它返回全部。

SQL Fiddle (感谢@JamieD77)

我想我应该使用这样的东西？但无法正确放置:

 WHERE NOT EXISTS 
 (SELECT p1.`Product Name` 
 FROM `products` p1 
 WHERE
 p.`Product Name` = 'nd')

我相信有一些更简单的方法可以告诉 MySQL 在查找匹配项时排除每列中的某些值。

Answer 1

我们首先返回“Phone1”。我们手头有 id 值，因此我们可以使用它。我们还可以使用 product_name 列。

 SELECT p.id
      , p.product_name
      , p.code_1
      , p.code_2
      , p.code_3
   FROM products p
  WHERE p.id = 1

很简单。

现在查找具有“匹配”代码的手机。我们可以从将 products 表连接到自身开始。

Phone1 将在其代码之一上匹配自身。 (假设至少有一个非 NULL 和非“nd”代码。)我们假设我们不想返回 Phone1，因此我们可以在连接谓词中排除它...仅返回 id 的行值(value)不同。

为了找到匹配的代码，我们可以将候选电话中的 code_1 与 Phone1 的 code_1、code_2 和 code_3 进行比较，看看其中是否有任何相同。我们可以对候选手机的 code_2 和 code_3 执行相同的操作。

规范确实告诉我们，nd 不应被视为匹配代码，因此如果我们要比较的 code_N 等于“nd”，我们可以简化比较。

根据规范，尚不清楚我们是否要考虑空字符串或空白，以等于另一个空白或空字符串的代码。 (我们无法判断这些空代码是 NULL 值、零长度字符串还是空格。)

而且，显然，我们希望从“匹配”电话返回列，而不是从 Phone1 行返回列。

像这样的事情应该可以解决问题:

 SELECT o.id
      , o.product_name
      , o.code_1
      , o.code_2
      , o.code_3
   FROM products p
   JOIN products o
     ON o.id <> p.id 
    AND (  (o.code_1 <> 'nd' AND o.code_1 IN (p.code_1,p.code_2,p.code_3) ) 
        OR (o.code_2 <> 'nd' AND o.code_2 IN (p.code_1,p.code_2,p.code_3) )
        OR (o.code_3 <> 'nd' AND o.code_3 IN (p.code_1,p.code_2,p.code_3) )
        )
  WHERE p.id = 1

根据连接谓词中的条件，o.code_N 中的 NULL 值将与 p.code_N 中的 NULL 值不匹配。如果我们还想忽略空字符串的匹配，我们可以稍微调整一下条件:

    AND (  (o.code_1 NOT IN ('nd','') AND o.code_1 IN (p.code_1,p.code_2,p.code_3) ) 
        OR (o.code_2 NOT IN ('nd','') AND o.code_2 IN (p.code_1,p.code_2,p.code_3) )
        OR (o.code_3 NOT IN ('nd','') AND o.code_3 IN (p.code_1,p.code_2,p.code_3) )
        )

就性能而言，这可能不会赢得任何比赛。

这并不是唯一返回指定结果的查询模式。我们可以编写一个使用 EXISTS 子句的查询来完成同样的事情:

 SELECT o.id
      , o.product_name
      , o.code_1
      , o.code_2
      , o.code_3
   FROM products o
  WHERE EXISTS 
        ( SELECT 1
           FROM phones p
          WHERE p.id = 1
            AND o.id <> p.id
            AND (  (o.code_1 <> 'nd' AND o.code_1 IN (p.code_1,p.code_2,p.code_3) ) 
                OR (o.code_2 <> 'nd' AND o.code_2 IN (p.code_1,p.code_2,p.code_3) )
                OR (o.code_3 <> 'nd' AND o.code_3 IN (p.code_1,p.code_2,p.code_3) )
                )
        )

如果有适当的索引，我们可以重写它以(可能)提高性能。

<小时/>

就更简单的事情而言，如果手机的 Code 1 、 Code 2 和 Code 3 作为单独的行存储在另一个表中，则 SQL 的编写方式会有所不同:

phone_code

phone_id  code_num  code_val
       1         1  MM
       1         2  DD
       1         3  nd
       2         1  DD
       2         2  
       2         3  nd
       3         1  MM
       3         2  ZZ
       3         3  nd
       4         1  XX
       4         2  
       4         3  nd

如果数据是这样构造的，SQL 代码将会有很大不同。