mysql-向上-向下-递归查询sql

mysql和oracle不同没有相关的递归查询的函数，要么自己写相应的函数（存过）要么写sql，以下是自己结合一篇博文，以及自己的项目实践。

前期准备：mysql数据库，数据字典表：

--表结构--
CREATE TABLE `csc_consult_servicetype` (
  `id` bigint(11) NOT NULL COMMENT '主键',
  `node_id` int(11) NOT NULL COMMENT '节点ID',
  `serv_type` int(11) NOT NULL COMMENT '业务类型细类，字典编码：，servtype',
  `node_parent_id` int(11) NOT NULL COMMENT '父亲节点ID',
  `node_code` varchar(100) NOT NULL COMMENT '节点编码',
  `node_name` varchar(100) NOT NULL COMMENT '节点名称',
  `node_jt_code` varchar(50) DEFAULT NULL COMMENT '节点集团编码',
  `node_jt_name` varchar(100) DEFAULT NULL COMMENT '节点集团名称',
  `node_stop` int(2) NOT NULL COMMENT '是否启用 ，字典编码：isorno',
  `node_memo` varchar(200) DEFAULT NULL COMMENT '备注',
  `node_pinyin` varchar(100) DEFAULT NULL COMMENT '节点拼音',
  `node_show` int(2) NOT NULL COMMENT '是否展示，字典编码：isorno',
  `sms_content` varchar(1000) DEFAULT NULL COMMENT '内网短信内容',
  `month_bill_send` bigint(20) NOT NULL COMMENT '服务选项',
  `gc_code` varchar(4) DEFAULT NULL COMMENT '集团服务指标编码',
  `dff_policy` varchar(4) DEFAULT NULL COMMENT '生效策略',
  `dff_date` datetime DEFAULT NULL COMMENT '生效时间',
  `order_id` int(11) NOT NULL COMMENT '顺序',
  `prefix` varchar(10) DEFAULT NULL COMMENT '前缀模板',
  `suffix` varchar(10) DEFAULT NULL COMMENT '后缀模板',
  `outer_net_code` varchar(500) DEFAULT NULL COMMENT '外网短信模板编码',
  `cst_content` varchar(1000) DEFAULT NULL COMMENT '来电完结原因',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

关键点是这四个字段：

其中首层节点的node_id = 0,node_parent_id=-1

1.向下递归：

关键sql-或者称为模板sql：

SELECT NODE_ID FROM(
        SELECT t1.NODE_ID,
        IF (
            find_in_set(NODE_PARENT_ID, @pids) > 0,
            @pids := concat(@pids, ',', NODE_ID),
            -1
        ) AS ischild
        FROM ( SELECT NODE_ID, NODE_PARENT_ID FROM csc_consult_servicetype t
                WHERE
                    t.NODE_STOP = 0 
          and t.NODE_SHOW = 0
          and t.SERV_TYPE=56
                ORDER BY
                    NODE_PARENT_ID,
                    NODE_ID
            ) t1,
            (
                SELECT
                    @pids := 15
            ) t2
    ) t3
WHERE
    ischild != -1

说明：

-- 某个节点的数据

SELECT SERV_TYPE,NODE_ID,NODE_PARENT_ID,NODE_CODE,NODE_NAME,NODE_JT_CODE,order_id from csc_consult_servicetype a 
where  a.NODE_STOP = 0 and a.NODE_SHOW = 0 and NODE_ID =15 and SERV_TYPE= 56 ORDER BY order_id;

查询该节点下的全部叶子节点

--查询向下的节点--
SELECT SERV_TYPE,NODE_ID,NODE_PARENT_ID,NODE_CODE,NODE_NAME,NODE_JT_CODE,order_id from csc_consult_servicetype a 
where  a.NODE_STOP = 0 and a.NODE_SHOW = 0 and a.SERV_TYPE=56 and
a.NODE_ID in (SELECT NODE_ID FROM(
        SELECT t1.NODE_ID,
        IF (
            find_in_set(NODE_PARENT_ID, @pids) > 0,
            @pids := concat(@pids, ',', NODE_ID),
            -1
        ) AS ischild
        FROM ( SELECT NODE_ID, NODE_PARENT_ID FROM csc_consult_servicetype t
                WHERE
                    t.NODE_STOP = 0 
          and t.NODE_SHOW = 0
          and t.SERV_TYPE=56
                ORDER BY
                    NODE_PARENT_ID,
                    NODE_ID
            ) t1,
            (
                SELECT
                    @pids := 15
            ) t2
    ) t3
WHERE
    ischild != -1);

结果：

 

2.向上递归查到首层的节点为止：

关键sql：

SELECT DISTINCT T2.NODE_ID
FROM ( 
   SELECT DISTINCT
       @r AS t1_id, 
       (SELECT @r:= NODE_PARENT_ID FROM csc_consult_servicetype WHERE 
          NODE_STOP = 0 
          and NODE_SHOW = 0
          and SERV_TYPE=56 
          and NODE_ID = t1_id) AS NODE_PARENT_ID
   FROM 
       (SELECT @r:= 125) vars, 
       csc_consult_servicetype h 
    WHERE @r <> -1) T1 
JOIN csc_consult_servicetype T2 
ON T1.t1_id = T2.NODE_ID

入参：@r:= 125 其他说明和向下递归类似

实例：

select SERV_TYPE,NODE_ID,NODE_PARENT_ID,NODE_CODE,NODE_NAME,NODE_JT_CODE,order_id
from csc_consult_servicetype 
where  NODE_STOP = 0 and NODE_SHOW = 0
          and SERV_TYPE=56 
          and NODE_ID = 125;

 

递归sql：

SELECT SERV_TYPE,NODE_ID,NODE_PARENT_ID,NODE_CODE,NODE_NAME,NODE_JT_CODE,order_id from csc_consult_servicetype a 
where  a.NODE_STOP = 0 and a.NODE_SHOW = 0 and a.SERV_TYPE=56 and
a.NODE_ID in(
SELECT DISTINCT T2.NODE_ID
FROM ( 
   SELECT DISTINCT
       @r AS t1_id, 
       (SELECT @r:= NODE_PARENT_ID FROM csc_consult_servicetype WHERE 
          NODE_STOP = 0 
          and NODE_SHOW = 0
          and SERV_TYPE=56 
          and NODE_ID = t1_id) AS NODE_PARENT_ID
   FROM 
       (SELECT @r:= 125) vars, 
       csc_consult_servicetype h 
    WHERE @r <> -1) T1 
JOIN csc_consult_servicetype T2 
ON T1.t1_id = T2.NODE_ID );

 

 如图 上层的每一个节点都找到。

参考资料：

向下递归：

https://www.cnblogs.com/rainydayfmb/p/8028868.html

mysql 递归查找菜单节点的所有子节点

背景                                                                                                      

项目中遇到一个需求，要求查处菜单节点的所有节点，在网上查了一下，大多数的方法用到了存储过程，由于线上环境不能随便添加存储过程，

因此在这里采用类似递归的方法对菜单的所有子节点进行查询。

准备                                                                                                        

创建menu表：

 

CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `parent_id` int(11) DEFAULT NULL COMMENT '父节点id',
  `menu_name` varchar(128) DEFAULT NULL COMMENT '菜单名称',
  `menu_url` varchar(128) DEFAULT '' COMMENT '菜单路径',
  `status` tinyint(3) DEFAULT '1' COMMENT '菜单状态 1-有效；0-无效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12212 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `menu` VALUES ('0', null, '菜单0', ' ', '1');
INSERT INTO `menu` VALUES ('1', '0', '菜单1', '', '1');
INSERT INTO `menu` VALUES ('11', '1', '菜单11', '', '1');
INSERT INTO `menu` VALUES ('12', '1', '菜单12', '', '1');
INSERT INTO `menu` VALUES ('13', '1', '菜单13', '', '1');
INSERT INTO `menu` VALUES ('111', '11', '菜单111', '', '1');
INSERT INTO `menu` VALUES ('121', '12', '菜单121', '', '1');
INSERT INTO `menu` VALUES ('122', '12', '菜单122', '', '1');
INSERT INTO `menu` VALUES ('1221', '122', '菜单1221', '', '1');
INSERT INTO `menu` VALUES ('1222', '122', '菜单1222', '', '1');
INSERT INTO `menu` VALUES ('12211', '1222', '菜单12211', '', '1');

 得到的目录结构如下图所示：

查询                                                                                                           

 先贴出sql语句：

1 2 3 4 5 6 7 8

select id 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 re_menu t where t.status = 1 order by parent_id, id                   ) t1,                   (select @pids := 要查询的菜单节点 id) t2              ) t3 where ischild != 0

 比如，要查询菜单节点12的所有子节点,则查处的结果为：

 分析                                                                                                            

首先分析from后面的语句，根据parent_id和id 排序，并将要查询的菜单节点当做变量，from后面的结果为

接下来看if（express1,express2,express3）条件语句，if语句类似三目运算符，当exprss1成立时，执行express2,否则执行express3;

FIND_IN_SET(str,strlist),str 要查询的字符串,strlist 字段名 参数以”,”分隔 如 (1,2,6,8),查询字段(strlist)中包含(str)的结果，返回结果为null或记录

如果parent_id 在@pid中，则将@pid 里面再加上parent_id,按行依次执行，执行过程如下表所示：

这时，显示的id就是菜单id为12的所有子节点id

 

向上递归：

http://blog.sina.com.cn/s/blog_8edc37a80101ij6c.html

mysql递归查询，mysql中从子类ID查询所有父类（做无限分类经常用到）

由于mysql 不支持类似 oracle with ...connect的 递归查询语法
之前一直以为类似的查询要么用存储过程要么只能用程序写递归查询.

现在发现原来一条sql语句也是可以搞定的

先来看数据表的结构如下：

id name   parent_id 
--------------------------- 
1  Home       0 
2  About      1 
3  Contact    1 
4  Legal      2 
5  Privacy    4 
6  Products   1 
7  Support    1 
我要的要求是根据一个分类ID（这个分类ID可能是一个子分类），得到所有的父分类，下面是相应的SQL：

SELECT T2.id, T2.name 
FROM ( 
   SELECT 
       @r AS _id, 
       (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, 
       @l := @l + 1 AS lvl 
   FROM 
       (SELECT @r := 5, @l := 0) vars, 
       table1 h 
    WHERE @r <> 0) T1 
JOIN table1 T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC 


代码@r := 5标示查询id为5的所有父类。结果如下
1, ‘Home’
2, ‘About’
4, ‘Legal’
5, ‘Privacy’