mysql无限递归查询
| user_id | name | parent_user_id |
| 0 | test-0 | null |
| 1 | test-1 | 0 |
| 2 | test-2 | 0 |
| 3 | test-3 | 1 |
| 4 | test-4 | 3 |
如图现有user表一张每条数据都与其他数据存在关联关系，现在有需求要查询id=0和下面所有的数据，Sql如下：
SELECT
u.*
FROM
user u,
(
select
d.user_ids
from
(
select
@a := concat( @a, ',', b.user_id ) user_ids
from
user b,
(select @a := 0) c
where
find_in_set( b.parent_user_id, @a ) > 0
) d
order by
length( d.user_ids) desc
limit 1
) e
where
find_in_set( u.user_id, e.user_ids )
如果只要id=0下面数据则可以这样写，Sql如下：
SELECT
u.*
FROM
user u,
(
select
substr(d.user_ids,length('0')+2) user_ids
from
(
select
@a := concat( @a, ',', b.user_id ) user_ids
from
user b,
(select @a := 0) c
where
find_in_set( b.parent_user_id, @a ) > 0
) d
order by
length( d.user_ids) desc
limit 1
) e
where
find_in_set( u.user_id, e.user_ids )
以上就是全部内容，有什么问题或者建议可以下面留言
