首页 > 专利查询

mysql省市区递归查询_mysql递归查询

mysql省市区递归查询_mysql递归查询1、创建表：

DROP IF EXISTS `t_areainfo`;

CREATE TABLE `t_areainfo` (

`id` (11) NOT '' AUTO_INCREMENT,

`level` int(11) DEFAULT '',

`name` varchar(255) DEFAULT '',

`parentId` int(11) DEFAULT '',

`` int(11) DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

2、初始数据：

INSERT INTO `t_areainfo` VALUES ('', '', '中国', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '华北区', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '华南区', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '海淀区', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '丰台区', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '朝阳区', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区1', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区2', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区3', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区4', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区5', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区6', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区7', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区8', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区9', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区10', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区11', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区12', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区13', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区14', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区15', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区16', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区17', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区18', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区19', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区1', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区2', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区3', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区4', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区5', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区6', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区7', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区8', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区9', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区10', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区11', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区12', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区13', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区14', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区15', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区16', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区17', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区18', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区19', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省1', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省2', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省3', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省4', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省5', '', ''); INSERT INTO `t_a

reainfo` VALUES ('', '', 'xx省6', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省7', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省8', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省9', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省10', '', ''); INSERT INTO `t_areainfo` VALUES ('', '', 'xx省11', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省12', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省13', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省14', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省15', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省16', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省17', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省18', '', '');

INSERT INTO `t_areainfo` VALUES ('', '', 'xx省19', '', '');

3、向下递归：

利⽤find_in_set()函数和group_concat()函数实现：

DROP FUNCTION IF queryChildrenAreaInfo;msinfo

CREATE FUNCTION queryChildrenAreaInfo(areaId INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';

SET sTempChd = CAST(areaId AS );

WHILE sTempChd IS NOT NULL DO

SET sTemp= CONCAT(sTemp,',',sTempChd);

SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0; END WHILE;

RETURN sTemp;

END;

4、调⽤⽅式：

SELECT queryChildrenAreaInfo(1);

查询id为"4"下⾯的所有节点

SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));

5、向上递归：

DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;

CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';

SET sTempChd = CAST(areaId AS CHAR);

SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;

WHILE sTempChd <> 0 DO

SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;

END WHILE;

RETURN sTemp;

END;

6、调⽤⽅式：

查询id为"7"的节点的所有上级节点：

SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));

包含递归查询⽗节点和⼦节点

包含mysql 递归查询⽗节点和⼦节点 mysql递归查询，查⽗集合，查⼦集合查⼦集合 --drop FUNCTION `getChildList` CREATE FUNCTION `getChi ...

MySQL递归查询_函数语法检查_GROUP_CONCAT组合结果集的使⽤

1-前⾔: 在Mysql使⽤递归查询是很不⽅便的，不像Sqlserver可以直接使⽤声明变量，使⽤虚拟表等等.如:DECLARE,BEGIN ... END

,WHILE ,IF 等等. 在My ...

MySQL递归查询树状表的⼦节点、⽗节点具体实现

mysql版本(5.5.6等等)尚未⽀持循环递归查询，和acle相⽐,mysql难于在树状表中层层遍历的⼦节点.本程序重点参考了下⾯的资料，写了两个sql存储过程,⼦节点查询算 ...

递归的实际业务场景之MySQL 递归查询

喜欢就点个赞呗! 源码

MySQL递归查询

MySQL8.0已经⽀持CTE递归查询，举例说明 CREATE TABLE EMP (EMPNO integer NOT NULL, ENAME ), JOB ), MGR integer, HIRE ...

MySQL递归查询所有⼦节点，树形结构查询

--表结构 CREATE TABLE `address` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code_value` varchar(32) DEFAUL ...

MySQL递归查询树状表的⼦节点、⽗节点

表结构和表数据就不公⽰了，查询的表user_role，主键是id，每条记录有parentid字段; 如下mysql查询函数即可实现根据⼀个节点查询所有的⼦节点，根据⼀个⼦节点查询所有的⽗节点.对于数据 ...

mysql 递归查询主要是对于层级关系的查询

最近遇到了⼀个问题，在mysql中如何完成节点下的所有节点或节点上的所有⽗节点的查询?在Oracle中我们知道有⼀个Hierarchical Queries可以通过CONNECT BY来查询，但是，在 ...

MySQL递归查询⽗⼦节点

1.表结构 CREATE TABLE folder( id BIGINT(20) NOT NULL, parent_id BIGINT(20) DEFAULT NULL, PRIMARY KEY id ...

随机推荐

LR录制脚本IE不能打开解决⽅法

运⾏环境:win7 64位解决⽅法:1.卸载IE11 2.计算机——属性——⾼级系统设置——性能⾥的设置——数据执⾏保护——选择“为除下列选定程序之外的所有程序和服务启⽤”——添加IE浏览器和VUG ...

js设计模式

www.csdn/article/2011-09-02/303983 阐明JavaScript设计模式.CSDN研发频道对此⽂进⾏了整理选取部分内容，供开发者学习.参考. 内容如 ...

CountDownLatch和CyclicBarrier的区别(转)

在⽹上看到很多⼈对于CountDownLatch和CyclicBarrier的区别简单理解为CountDownLatch是⼀次性的,⽽CyclicBarrier在调⽤reset 之后还可以继续使⽤.那如 ...

openssl使⽤+Demo

1. websiteSSL(secure Socket Layer)TLS(transport Layer Security) - SSL3.0基础之上提出的安全通信标准,⽬前版本是1.0openss ...

A Bug's Life(种类并查集)(也是可以⽤dfs做)

acm.hdu.edu/showproblem.php?pid=1829 A Bug's Life Time Limit:5000MS Memory Limit:327 ...

VSCode插件MSSQL教程(昨天提了⼀下)

推荐⼀个跨平台SQL IDE:docs.microsoft/zh-cn/sql/sql-operations-studio/download 什么数据库都⽊有(系统⾃带的不算) ...

本文发布于:2024-09-21 20:45:33，感谢您对本站的认可！

本文链接：https://www.17tex.com/tex/4/380007.html

上一篇：Java自动化测试系列[v1.0.0][ZTestReport测试报告]

下一篇：消抖函数

标签：查询节点递归程序参考计算机变量设计模式

留言与评论（共有 0 条评论）