Mysql查询树形结构栏目

一、查询一个栏目所有父级栏目

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

上述代码中带中文的地方要根据实际数据库结构修改