mysql 根据父id 递归查询所有的子类

方法一、mysql中建立递归查询方法

-- DROP FUNCTION `getChildrenOrg` 
CREATE FUNCTION `getChildrenOrg`(uuid INT) RETURNS text CHARSET utf8
BEGIN
	DECLARE sTemp text;
	DECLARE sTempChd text;

	SET sTemp = '$';
	SET sTempChd =cast(uuid as CHAR);
WHILE sTempChd is not null DO

	SET sTemp = concat(sTemp,',',sTempChd);
	SELECT group_concat(id) INTO sTempChd FROM tb_club_member where FIND_IN_SET(parentId,sTempChd)>0;

END WHILE;
	RETURN sTemp;
END

以上text类型可以改成varchar(1000)，如果id长度太长，建议使用text

查询如下：

SELECT * FROM tb_club_member WHERE FIND_IN_SET(id,getChildrenOrg('2'))

方法二、SQL查询

SELECT org_id
FROM (
  SELECT t1.org_id, IF ( FIND_IN_SET(parent_org_id, @pids) > 0, @pids := CONCAT(@pids, ',', org_id), 0) AS ischild
  FROM (SELECT org_id, parent_org_id
	FROM wcm_orm_org t
	ORDER BY parent_org_id, org_id
	) t1,
	(SELECT @pids := '10000001') t2
) t3
WHERE ischild != 0 OR org_id = '10000001'

其中org_id为查询id，parent_org_id为父id，wcm_orm_org为表名，'10000001'为要查询的id值。最后可以加上OR org_id = '10000001' 表示查询结果包括自己，也可以不加。  

方法三、SQL查询

SELECT level, DATA.* FROM( 
SELECT 
@ids as _ids, 
( SELECT @ids := GROUP_CONCAT(dept_id) 
FROM ding_orm_dept 
WHERE FIND_IN_SET(parent_dept_id, @ids) 
) as cids, 
@l := @l+1 as level 
FROM ding_orm_dept, 
(SELECT @ids :='130957780', @l := 0 ) b 
WHERE @ids IS NOT NULL 
) dept_id, ding_orm_dept DATA 
WHERE FIND_IN_SET(DATA.dept_id, _ids) 
ORDER BY level, dept_id

查询ding_orm_dept表中id=‘130957780’的所有下级数据