mysql临时表自连接,如何在MySQL中进行递归自联接

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.

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`

My table Category looks like this:

id - Obvious

label - id of a name in another table

parent - the id of the above laying Category

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

解决方案

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.