mysql儿子数据库,MYSQL-从父母那里得到所有孩子

I have stuck in getting all children's query where parent id greater than the customer id

table test

id name parent

1 test1 0

2 test2 1

3 test3 1

4 test4 2

5 test5 2

6 test6 10

7 test7 10

8 test8 6

9 test9 6

10 test10 5

11 test10 7

Currently I am using this recursive query but it shows children till the 10 parent but not able to give children of 6 and 7 and further

SELECT id , parent FROM (SELECT id , parent from (SELECT * FROM test order by

parent , id) testdata_sorted, (SELECT @pv := '1') initialisation where

find_in_set(parent , @pv) > 0 and @pv := concat(@pv, ',', id) ORDER BY

parent ASC) AS tt

Current Output is ->

id parent

2 1

3 1

4 2

5 2

10 5

6 10

7 10

I need this Type of output . I need help out in this regard .

id parent

2 1

3 1

4 2

5 2

10 5

6 10

7 10

8 6

9 6

11 7

解决方案

You are using a fragile way to simulate a recursive query. It specifically requires that a parent row has to be sorted before the child.

Your base rowset is using order by parent, id:

id parent

----------------------

1 0

2 1 -- fine

3 1 -- fine

4 2 -- fine

5 2 -- fine

10 5 -- fine

8 6 -- parent 6 comes later!

9 6 -- parent 6 comes later!

11 7 -- parent 7 comes later!

6 10 -- fine

7 10 -- fine

You see that those are exactly the rows that are missing from your result.

There is no simple fix to this, as to order your rows on the fly to be able to be used in your recursive query, you need a recursive query. You may be able to enter your data in a way that fulfills that condition though. While I assume that the part where parent id greater than the customer id in your question is actually not a condition (as your expected output does not align with that): if you have such a condition that constraints parent and child, it could give you a possible order.

For alternative ways to model your data or write your query, see How to create a MySQL hierarchical recursive query. Actually, trincots answer includes a remark about the order requirement for your code.

Preferably, you would be using a version that supports recursive CTEs, because as long as you do not want to change your data model, every workaround for those has some limitations (e.g. row order or max depth).

A side note: order by in a subquery (specifically testdata_sorted) can be ignored by MySQL, and you may have to verify that it doesn't (which can depend on things like version, indexes or table sizes).