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 )
以上就是全部内容,有什么问题或者建议可以下面留言