通过MySQL中的父ID和where子句获取所有子代 [英] Get all children by parent id and where clause in mysql

问题描述

我有一个表，在同一表中存储id和parent_id.我想要一个递归查询，它接受parent_id作为参数并返回所有具有第n级的子节点.为此，我正在使用此代码并为我正常工作.

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)

我的问题从这里开始:我想添加带有结果集的WHERE子句，但无法执行此操作.在结果集中，我得到的用户类型为'admin', 'editor'.

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'.

我想从结果集中删除'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:

因此，即使孩子不是编辑者，如果他们的祖先之一是编辑者，也应将他们排除在外.这意味着您应该在最里面的查询中排除记录:在其中添加where:

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.

在select列表中添加user_type字段，然后包装执行过滤器的查询，如下所示:

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).

这篇关于通过MySQL中的父ID和where子句获取所有子代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！