MySQL父查询子、子查询父

谁借莪１个温暖的怀抱￠ 2023-07-13 03:21 26阅读 0赞

### 一、根据父查询子 ###

**1、sql实现**

SELECT GROUP_CONCAT(id) id FROM (
    		SELECT t1.*,IF(FIND_IN_SET(parentid, @pids) > 0, @pids := CONCAT(@pids, ',', id), '0') AS ischild
    		FROM (SELECT * FROM test ORDER BY id ASC) t1,(SELECT @pids := '变量父ID') t2
    ) t3 WHERE ischild != '0'

**需修改的变量：**id：主键id、parentid：父id、test：表名。

**2、函数实现**

2.1、创建函数

CREATE DEFINER=`bd_db_admin`@`%` FUNCTION `getChildId`(`pId` bigint) RETURNS varchar(4000) CHARSET utf8
    BEGIN
    	#Routine body goes here...
    	DECLARE ids VARCHAR(4000);
    	DECLARE childId VARCHAR(4000);
    
    	SET ids = '$';
    	SET childId = CAST(pId AS CHAR);
    	
    	WHILE childId is NOT NULL DO
    	SET ids = CONCAT(ids,',',childId);
    	select GROUP_CONCAT(id) INTO childId FROM test WHERE FIND_IN_SET(parentid,childId)>0;
    	END WHILE;
    
    	RETURN ids;
    END

2.2、使用

select getChildId(122)

### 二、根据子id查询父id ###

SELECT t2.* FROM ( 
        SELECT @r AS _id,  (SELECT @r := parentid FROM test WHERE id = _id) AS parentid, @l := @l + 1 AS lvl 
        FROM (
    			SELECT @r := '要查询的ID', @l := 0) vars, test
        WHERE @r <> 0
    ) t1 JOIN test t2 ON t1._id = t2.id 
    ORDER BY t1.lvl DESC

**需修改的变量： **id：主键、parentid：父id、test：表名。

**欢迎关注**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R5cDE4MDU_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R5cDE4MDU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200419193206685.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R5cDE4MDU=,size_16,color_FFFFFF,t_70