mysql - 如何加入表格？

Question

我有三个表:

部分:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

零件编号:

+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| part_id   | int(10) unsigned | NO   | PRI | NULL    |       |
| partid_id | int(10) unsigned | NO   | PRI | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
+---------+-----------+
| part_id | partid_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       1 |         3 |
|       2 |         1 |
|       2 |         4 |
|       2 |         5 |
|       3 |         6 |
|       3 |         7 |
|       3 |         8 |
+---------+-----------+

部分:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| key   | varchar(50)      | NO   | MUL | NULL    |                |
| value | varchar(50)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
+----+-----+-------+
| id | key | value |
+----+-----+-------+
|  1 | l   | foo   |
|  2 | p   | bar   |
|  3 | s   | baz   |
|  4 | l   | 3     |
|  5 | p   | 5     |
|  6 | s   | r     |
|  7 | l   | rr    |
|  8 | l   | rrr   |
+----+-----+-------+

基本上每个部分都由任意部分 ID 组成。例如第 1 部分有 l = 'foo'、p = 'bar' 和 s = 'baz'。

如果我想搜索所有 l = 'foo' 的部分，我使用这个连接

SELECT p.id FROM parts AS p
INNER JOIN parts_partid ppi ON ppi.part_id = p.id
INNER JOIN partid pi ON ppi.partid_id = pi.id
WHERE pi.key = 'l' AND pi.value = 'foo';

这给了我第 1 部分和第 2 部分。

现在我想搜索具有 l = 'foo' 和 p = '5' 的所有部分(这应该只会导致第 2 部分)。我该怎么做？

Answer 1

试试这个:

SELECT id
FROM
(
  SELECT 
    p.id,
    MAX(CASE WHEN pi.Key = 'l' THEN value END) AS l,
    MAX(CASE WHEN pi.Key = 'p' THEN value END) AS p,
    MAX(CASE WHEN pi.Key = 's' THEN value END) AS s
  FROM parts AS p
  INNER JOIN parts_partid ppi ON ppi.part_id = p.id
  INNER JOIN partid pi ON ppi.partid_id = pi.id
  GROUP BY p.id
) AS t
WHERE l = 'foo' 
  AND p = 5;

SQL Fiddle Demo

这只会给你第 2 部分:

Results :

| ID |
------
|  2 |

---

此查询背后的想法是子查询，它将将键的值转换为列；它会给你每个 id 的那些键作为列标题:

| ID |   L |      P |      S |
------------------------------
|  1 | foo |    bar |    baz |
|  2 | foo |      5 | (null) |
|  3 | rrr | (null) |      r |

然后在外部查询中，您可以过滤这些列的值。