Mysql查询树形列表下级所有子级

数据库部门表

 

 sql语句

//dp_id 部门id
//dp_name 部门名称
//p_dept_no 父级id
//dp_num 部门编号
WITH RECURSIVE depLit(dp_id, dp_name, p_dept_no, dp_num) AS (
	SELECT
		dp_id,
		dp_name,
		p_dept_no,
		dp_num AS path 
	FROM
		m_department 
	WHERE
		p_dept_no = 0 
    UNION ALL
	SELECT
		b.dp_id,
		b.dp_name,
		b.p_dept_no,
		concat( a.dp_num, "-", b.dp_num) AS path 
	FROM
		depLit a,
		m_department b 
	WHERE
		a.dp_id = b.p_dept_no 
) 
SELECT dp_id, dp_name, p_dept_no, dp_num FROM depLit;

从上至下查询，查询结果