Code Snippet - 2020年4月
type: channel
link: https://blog.chinapod.net/2020/04/
description: Just So So ...
items: Mysql查询树形结构栏目

Mysql查询树形结构栏目
type: item
date: 2020-04-04T01:39:00+00:00
description: 一、查询一个栏目所有父级栏目 SELECT T1.lvl, T2.* FROM ( SELECT @r AS _id, ( SELECT @r := `上级ID字段名` FROM `栏目表名` WHERE `栏目ID字段名` = _id ) AS parent_id, @l := @l + 1 AS lvl FROM ( SELECT @r := `要查询的栏目ID值`, @l := 0 ) vars, `栏目表名` h WHERE @r <> 0 ) T1 JOIN `栏目表名` T2 ON T1._id = T2.`栏目ID字段名` ORDER BY `栏目ID字段名`;二、查询一个栏目所有子级栏目 SELECT * FROM ( SELECT t1.*, CASE WHEN `栏目ID字段名` = @pids THEN 1 WHEN find_in_set( `上级ID字段名`, @pids ) > 0 THEN @pids := concat( @pids, ',', `栏目ID字段名`) ELSE 0 END ischild FROM ( SELECT * FROM `栏目表名` t ORDER BY `上级ID字段名`, `栏目ID字段名` ) t1, ( SELECT @pids := 要查询的栏目 ID值 ) t2 ) t3 WHERE ischild != 0上述代码中带中文的地方要根据实际数据库结构修改