如何在MySQL中进行递归自联接 [英] How to mak a recursive self join in MySQL

问题描述

我正在建立一个网上商店，需要产生一个收集列表. 问题是我有3种产品和一个递归表(无限). 分别是产品，选项和类别，类别是递归的.

I'm building a webshop which needs to produce a gatherlist. The problem is that i have 3 kinds of products and a recursive table (infinite). namely Products, Option and Category, were category's are the recursive ones.

我正在尝试使用字段Order.id，OrderProduct.product，Component.id构建视图.

I'm trying to build a view with the fields Order.id, OrderProduct.product, Component.id.

在这里您可以看到我已经拥有的内容，但是我通过选项删除了-的一部分，因为它不会提供"示例.

Here you can see what i've already, however i removed the part of -- by option as it doesn't 'feed' the example.

CREATE VIEW `Gatherlist` as
SELECT `O`.`id`, `OP`.`product`, `C`.`id` FROM `Order` `O` -- By Product
LEFT JOIN `OrderProduct` `OP`
    ON `O`.`id` = `OP`.`order`
LEFT JOIN `Product` `P`
    ON `OP`.`product` = `P`.`id`
LEFT JOIN `ProductComponent` `PC`
    ON `P`.`id` = `PC`.`product`
LEFT JOIN `Component` `C`
    ON `PC`.`component` = `C`.`id`
UNION ALL
SELECT `O`.`id`, `OP`.`product`, `C`.`id` FROM `Order` `O` -- By Category
LEFT JOIN `OrderProduct` `OP`
    ON `O`.`id` = `OP`.`order`
LEFT JOIN `Product` `P`
    ON `OP`.`product` = `P`.`id`
-- LEFT JOIN `Category` `PC`
    -- ON `P`.`category` = `PC`.`id`
    -- Here should start a recursive join on Category
    INNER JOIN (
        SELECT group_concat(@id :=
            (
                SELECT id
                FROM `Category`
                WHERE parent = @id
                )
            ) AS categoryTemp
        FROM (
            SELECT @id := 1
        ) vars
        STRAIGHT_JOIN
            `Category`
        WHERE @id IS NOT NULL
    ) as `PC` on `P`.`category` = `PC`.`id`
    -- And it will end somewhere again (i hope)
LEFT JOIN `CategoryCompenent` `CC`
    ON `PC`.`id` = `CC`.`category`
LEFT JOIN `Component` `C`
    ON `CC`.`component` = `C`.`id`
ORDER BY `O`.`id`, `OP`.`product`, `C`.`id`

我的表格类别如下:

• id-明显
• 标签-另一个表中名称的ID
• 父级-以上铺设类别的ID

我发现了这一点，但无法弄清楚如何将其放入联接中(在具有联合的视图内).

I have found this but could not figure out how to put it in a join (within a view with unions).

SELECT  group_concat(@id :=
    (
    SELECT  id
    FROM    comments
    WHERE   parent_id = @id
    )) AS comment
FROM    (
    SELECT  @id := 1
    ) vars
STRAIGHT_JOIN
    comments
WHERE   @id IS NOT NULL

推荐答案

在MySQL中没有真正的方法(至少据我所知)进行递归"联接.如果您具有这样的表结构，那么我所知道的最好的解决方案是使用存储过程来循环并收集"相关的行和/或构建路径".不幸的是，您不能加入存储过程的结果，因此通常意味着将其放到执行该过程之后要使用的数据到预定的临时表中.

There is no real way (at least that I know of) to do a "recursive" join in MySQL. If you have such a table structure, the best solution I know of is using stored procedures to loop through and "collect" related rows and/or build "paths"; unfortunately, you cannot join to the results of a stored procedure, so that usually means having it drop that data into a predetermined temp table to be used after the procedure has executed.

或者，您可以在代码中分析递归表以确定其当前的深度"，以便以编程方式组成查询.

Alternately, you can analyse the recursive table in code to determine its current "depth" in order to compose the query programmatically.

这篇关于如何在MySQL中进行递归自联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！