MySQL 在链接字段匹配时选择行

Question

我正在尝试构建一个查询，从中获取内容与搜索相匹配的所有变体。例如，基于下表，我想获取包含 content = "red"的所有变体

 -------------------------------------------
| ID | Product | Variant | Option | Content |
 -------------------------------------------
|  1 | Shirt   | 1       | size   | S       |   
|  2 | Shirt   | 1       | color  | red     |
|  3 | Shirt   | 2       | size   | M       |   
|  4 | Shirt   | 2       | color  | red     |
|  5 | Shirt   | 3       | size   | L       |   
|  6 | Shirt   | 3       | color  | red     |
|  7 | Shirt   | 4       | size   | M       |
|  8 | Shirt   | 4       | color  | blue    |
 -------------------------------------------

我希望结果是这样的:

 -------------------------------------------
| ID | Product | Variant | Option | Content |
 -------------------------------------------
|  1 | Shirt   | 1       | size   | S       |   
|  2 | Shirt   | 1       | color  | red     |
|  3 | Shirt   | 2       | size   | M       |   
|  4 | Shirt   | 2       | color  | red     |
|  5 | Shirt   | 3       | size   | L       |   
|  6 | Shirt   | 3       | color  | red     |
 -------------------------------------------

当 content = M 时，结果是这样的

 -------------------------------------------
| ID | Product | Variant | Option | Content |
 -------------------------------------------
|  3 | Shirt   | 2       | size   | M       |   
|  4 | Shirt   | 2       | color  | red     |
|  7 | Shirt   | 4       | size   | M       |
|  8 | Shirt   | 4       | color  | blue    |
 -------------------------------------------

我希望这是有道理的。谢谢!

我做了一个 fiddle http://sqlfiddle.com/#!9/63e64e/2

Answer 1

使用自连接

SELECT v1.*
FROM variant AS v1
JOIN variant AS v2 ON v1.variant = v2.variant
WHERE v2.content = 'red'

这在功能上等同于 Tin Tran 的查询，但不需要编写子查询，因此更简单一些。您需要检查 EXPLAIN 输出以确保它们的处理方式相同。