mysql 父id求所有子id,通过MySQL中的父ID和where子句获取所有子代

I have a table that stores id and parent_id in same table. I want a recursive query that accepts parent_id as an argument and returns all child nodes with nth level. For this I am using this code and working properly for me.

select id,

name,

parent

from (select * from tablename

order by parent, id) tablename,

(select @pv := '1') initialisation

where find_in_set(parent, @pv) > 0

and @pv := concat(@pv, ',', id)

My problem start from here: I want to add WHERE clause with result set but unable to do this. In result set I am getting user type like 'admin', 'editor'.

I want to remove 'editor' user type from result set. Let me know if possible to how to get this?

解决方案

There are two interpretations possible. From a recent comment I understand you need the first one:

Exclude children of excluded parents

So even if children are not editors, if one of their ancestors is an editor they should be excluded. That means you should exclude records in the inner most query: add the where there:

select id,

name,

parent_id,

user_type

from (select * from p

where user_type <> 'editor'

order by parent_id, id) products_sorted,

(select @pv := '19') initialisation

where find_in_set(parent_id, @pv)

and length(@pv := concat(@pv, ',', id))

Include children of excluded parents

In this interpretation you want editor children to be included irrespective of whether any of their ancestors are to be excluded.

Add the user_type field in the select list and then wrap that query that performs the filter, like this:

select *

from (

select id,

name,

parent_id,

user_type

from (select * from p

order by parent_id, id) products_sorted,

(select @pv := '19') initialisation

where find_in_set(parent_id, @pv)

and length(@pv := concat(@pv, ',', id))

) as sub

where user_type <> 'editor'

So again, here the result will include also records of which the parent-hierarchy (parent, grandparent, grand-grandparent, ...) might not be completely included (because some of those could be editor).