mysql 根据父id查询下面的子id

DROP TABLE IF EXISTS `t_org`;
CREATE TABLE `t_org` (
`id` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`parent_id` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_org
-- ----------------------------
INSERT INTO `t_org` VALUES ('ddd', '3', 'rty');
INSERT INTO `t_org` VALUES ('bbb', '3', 'rty');
INSERT INTO `t_org` VALUES ('ee', '3', 'rty');
INSERT INTO `t_org` VALUES ('aa', '4', 'ee');
INSERT INTO `t_org` VALUES ('rt', '2', 'zttt');
INSERT INTO `t_org` VALUES ('qqq', '4', 'ee');
INSERT INTO `t_org` VALUES ('fghh', '4', 'ee');
INSERT INTO `t_org` VALUES ('wew', '2', 'rttt');
INSERT INTO `t_org` VALUES ('jkl', '4', 'ee');
INSERT INTO `t_org` VALUES ('rty', '2', 'zttt');
INSERT INTO `t_org` VALUES ('qwe', '3', 'rty');
INSERT INTO `t_org` VALUES ('vbn', '3', 'rty');
INSERT INTO `t_org` VALUES ('zttt', '1', 'zwew');

SET FOREIGN_KEY_CHECKS = 1;

 

 

SELECT
id,
ischild
FROM
(
SELECT
t1.id,
IF
( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild
FROM
( SELECT id, parent_id FROM t_org t ORDER BY level,parent_id, id ) t1,
( SELECT @pids := 'aaa' ) t2
) t3
WHERE
ischild != '0'

 

sql如上面所示，网上找的sql不加level，这样会有bug，当id和parent_id是uuid时，order by不能根据顺序排列，这样‘aaa’有可能不会最上面，concat里面就没有这个‘aaa’，就会有问题，加上level以后，就会解决这个问题。数据在上面，可以自己试试，不加level和加level的区别

----------------------------------------------------------------

新方法

统计根据父节点查子节点的sql

WITH recursive t AS (
SELECT
*
FROM
category
WHERE
f_id = '0834eeab-5e5b-4157-84cd-a7c9757e2a4d' UNION ALL
SELECT
a.*
FROM
category a
JOIN t ON t.f_id = a.f_parent_id
WHERE
a.f_flag = 0
AND a.f_level > 0
) SELECT
f_name,
f_level
FROM
t
ORDER BY
f_level

统计根据子节点查父节点的sql

WITH recursive t AS (
    SELECT *
    FROM km_category
    WHERE f_id = #{id}
    UNION ALL
    SELECT a.*
    FROM km_category a
             JOIN t ON a.f_id = t.f_parent_id
    where a.f_flag = 0
      and a.f_level > 0
)
SELECT f_name,f_level
FROM t
ORDER BY f_level
--------------------------------------------------------
mysql 8.0以后新方法
根据父节点id查询子节点数据

SELECT *
FROM (
WITH recursive t AS (
SELECT *
FROM cc_om_organ
WHERE ORGAN_ID = 'O1132'
UNION ALL
SELECT a.*
FROM cc_om_organ a
JOIN t ON a.ORGAN_ID = t.PARENT_ID
)
SELECT ORGAN_ID
FROM t
) aa