mysql - 从 mysql 连接中的不同行选择值

Question

我有两个表:products 和 categories，以及一个联接表 products_categories。

类别是嵌套的(通过categories.parent_id)。因此，对于任何给定的产品，它可以属于许多潜在的嵌套类别。

类别的结构如下:

• 部门(深度 = 0)
  • 类别(深度 = 1)
    • 类别(深度 = 2)

每个产品将属于一个“部门”、一个“类别”和一个“类”。

因此，像“Rad Widget”这样的产品可能属于“电子”部门、“杂项”类别和“小部件”类别。我的架构如下所示:

# products

id | name
---------------
1  | Rad Widget


# categories

id | parent_id | depth | name
--------------------------------------
1  | null      | 0     | Electronics
2  | 1         | 1     | Miscellaneous
3  | 2         | 2     | Widgets

# products_categories

product_id | category_id
------------------------
1          | 1
1          | 2
1          | 3

我想运行一个查询，将产品的所有部门列出到一行中，如下所示:

product.id | product.name | department  | category      | class
-----------------------------------------------------------------
1          | Rad Widget   | Electronics | Miscellaneous | Widgets

我想不出一种方法来做到这一点，所以我正在考虑对我的数据进行非规范化，但我想首先确保我没有遗漏一些东西。

Answer 1

由于每个类别(顺便说一句，您可能想要重命名表或级别，以便“类别”并不意味着两个不同的事物)都有一个已知的单一父级，但有不确定数量的未知子级，您需要从最具体的(深度= 2)“向上”到最一般的类别，对每个附加值在category表上执行自连接你想插入。

如果您不耐烦，请跳至帖子底部的 SQL Fiddle 链接。如果您希望逐步了解它，请继续阅读 - 这实际上与任何其他情况没有什么不同，您有一个代理 ID，您希望将其替换为相应表中的数据。

您可以首先查看所有信息:

SELECT * FROM products AS P
        JOIN
    products_categories AS PC ON P.id = PC.product_id
        JOIN
    categories AS C ON PC.category_id = C.id
WHERE P.id = 1 AN D C.depth = 2;

+----+------------+------------+-------------+----+-----------+-------+---------+
| id | name       | product_id | category_id | id | parent_id | depth | name    |
+----+------------+------------+-------------+----+-----------+-------+---------+
| 1  | Rad Widget | 1          | 3           | 3  | 2         | 2     | Widgets |
+----+------------+------------+-------------+----+-----------+-------+---------+

您要做的第一件事是识别哪些信息有用，哪些信息无用。您不想整天在这里SELECT *。您拥有所需的前两列和最后一列(将其识别为您的“类”)；您需要 parent_id 来查找所需的下一列，让我们保留 深度 只是为了说明。忘记其余的，它们很困惑。

因此，将 * 替换为特定的列名称、别名“class”，并查找由 parent_id 表示的数据。此信息存储在 category 表中 - 您可能会想，但我已经加入了该表!不在乎；再做一次，只给它一个新的别名。请记住，您的 ON 条件有点不同 - products_categories 已经完成了它的工作，现在您需要匹配 C.parent_id 的行 -并且您只需要某些列即可找到下一个父级:

SELECT
    P.id,
    P.name,
    C1.parent_id,
    C1.depth,
    C1.name,
    C.name AS 'class'
FROM
    products AS P
        JOIN
    products_categories AS PC ON P.id = PC.product_id
        JOIN
    categories AS C ON PC.category_id = C.id
        JOIN
    categories AS C1 ON C.parent_id = C1.id
WHERE
    P.id = 1
        AND C.depth = 2;

+----+------------+-----------+---------------+---------+
| id | name       | parent_id | name          | class   |
+----+------------+-----------+---------------+---------+
| 1  | Rad Widget | 1         | Miscellaneous | Widgets |
+----+------------+-----------+---------------+---------+

再次重复该过程，为您刚刚添加的列添加别名，并在下一个连接条件中使用新的 C1.parent_id:

SELECT
    P.id,
    P.name,
    PC.category_id,
    C2.parent_id,
    C2.depth,
    C2.name,
    C1.name AS 'category',
    C.name AS 'class'
FROM
    products AS P
        JOIN
    products_categories AS PC ON P.id = PC.product_id
        JOIN
    categories AS C ON PC.category_id = C.id
        JOIN
    categories AS C1 ON C.parent_id = C1.id
        JOIN
    categories AS C2 ON C1.parent_id = C2.id
WHERE
    P.id = 1
        AND C.depth = 2;

+----+------------+-----------+-------+-------------+---------------+---------+
| id | name       | parent_id | depth | name        | category      | class   |
+----+------------+-----------+-------+-------------+---------------+---------+
| 1  | Rad Widget | NULL      | 0     | Electronics | Miscellaneous | Widgets |
+----+------------+-----------+-------+-------------+---------------+---------+

现在我们显然已经完成了；我们无法在 C2.parent_id = NULL 上加入另一个副本，而且我们还看到 深度 = 0，因此剩下要做的就是删除我们不添加的列不想显示并仔细检查我们的别名。这是 in action on SQL Fiddle .