mysql的逆袭:如何做递归层次查询
来源:互联网 发布:apache错误日志在哪 编辑:程序博客网 时间:2024/05/16 11:47
最近在做一个从oracle数据库到mysql数据库的移植,遇到一个这样的问题
在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但shi,在MySQL的目前版本中还没有对应的函数!!!
换句话来说,想要用mysql实现递归查询,根本做不到!!!
可是经过我数天茶不思饭不想的刻苦琢磨,终于想到了一个合理的,适用于mysql和其他sql的解决方案。
www.2cto.com
方案一出,就秋风扫落叶之势,席卷整个dao层~~~所到之处,所有问题迎刃而解,让所有问题都不再为问题 都成为了我这个函数的炮灰而已。。。
话不多说待我把解决方法仔细道来~~~~~
下面是sql脚本,想要运行一下 把下边的粘贴复制下来,做一个treenodes.sq直接运行便是。。。
/*
Navicat MySQL Data Transfer
Source Server : mysql_demo3
Source Server Version : 50521
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50521
File Encoding : 65001
www.2cto.com
Date: 2012-09-02 21:16:03
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `treenodes`
-- ----------------------------
DROP TABLE IF EXISTS `treenodes`;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(200) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
-- ----------------------------
-- Records of treenodes
-- ----------------------------
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');
INSERT INTO `treenodes` VALUES ('8', 'H', '0');
INSERT INTO `treenodes` VALUES ('9', 'I', '8');
INSERT INTO `treenodes` VALUES ('10', 'J', '8');
INSERT INTO `treenodes` VALUES ('11', 'K', '8');
INSERT INTO `treenodes` VALUES ('12', 'L', '9');
INSERT INTO `treenodes` VALUES ('13', 'M', '9');
INSERT INTO `treenodes` VALUES ('14', 'N', '12');
INSERT INTO `treenodes` VALUES ('15', 'O', '12');
INSERT INTO `treenodes` VALUES ('16', 'P', '15');
INSERT INTO `treenodes` VALUES ('17', 'Q', '15');
www.2cto.com
---------------------------------------------------
上边是sql脚本,在执行select * 之后显示的结果集如下所示:
mysql> select * from treenodes;
+----+----------+------+
| id | nodename | pid |
+----+----------+------+
| 1 | A | 0 |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 6 |
| 8 | H | 0 |
| 9 | I | 8 |
| 10 | J | 8 |
| 11 | K | 8 |
| 12 | L | 9 |
| 13 | M | 9 |
| 14 | N | 12 |
| 15 | O | 12 |
| 16 | P | 15 |
| 17 | Q | 15 |
+----+----------+------+
17 rows in set (0.00 sec)
树形图如下
1:A
+-- 2:B
| +-- 4:D
| +-- 5:E
+-- 3:C
+-- 6:F
+-- 7:G
8:H
+-- 9:I
| +-- 12:L
| | +--14:N
| | +--15:O
| | +--16:P
| | +--17:Q
| +-- 13:M
+-- 10:J
+-- 11:K
--------------------------------------------
如果给你一个这样的table,让你查询根节点为1下的所有节点记录(注意也包括根节点),,肿麽办?????
可能有不少人想到connect by 函数,但是我灰常遗憾的告诉你,咱这儿是mysql!!!
好,客观您勒上眼,,我的解决办法是
利用函数来得到所有子节点号。
闲话少续,看我的解决方法
创建一个function getChildLst, 得到一个由所有子节点号组成的字符串.
mysql> delimiter //
mysql>
mysql> CREATE FUNCTION `getChildLst`(rootId INT)
-> RETURNS varchar(1000)
-> BEGIN
-> DECLARE sTemp VARCHAR(1000);
-> DECLARE sTempChd VARCHAR(1000);
->
-> SET sTemp = '$';
-> SET sTempChd =cast(rootId as CHAR);
->
-> WHILE sTempChd is not null DO
-> SET sTemp = concat(sTemp,',',sTempChd);
-> SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
-> END WHILE;
-> RETURN sTemp;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
www.2cto.com
使用我们直接利用find_in_set函数配合这个getChildlst来查找
mysql> select getChildLst(1);
+-----------------+
| getChildLst(1) |
+-----------------+
| $,1,2,3,4,5,6,7 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from treeNodes
-> where FIND_IN_SET(id, getChildLst(1));
+----+----------+------+
| id | nodename | pid |
+----+----------+------+
| 1 | A | 0 |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 6 |
+----+----------+------+
7 rows in set (0.01 sec)
mysql> select * from treeNodes
-> where FIND_IN_SET(id, getChildLst(3));
+----+----------+------+
| id | nodename | pid |
+----+----------+------+
| 3 | C | 1 |
| 6 | F | 3 |
| 7 | G | 6 |
+----+----------+------+
3 rows in set (0.01 sec)
--------------------------------------------
只要按我的做,百发百中弹无虚发,遇到问题万变不离其宗直接粘贴复制就是。。。
补充:
还可以做嵌套查询:
select id,pid from treeNodes where id in(
select id from treeNodes where FIND_IN_SET(id, getChildLst(3))
);
子查询的结果集是
www.2cto.com
+--------+
id
----
3
6
7
+-------+
然后经过外层查询就是
id pid
3 1
6 3
6 6
---------
好了 Perfect
--》
SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `treenodes`-- ----------------------------DROP TABLE IF EXISTS `treenodes`;CREATE TABLE `treenodes` ( `id` int(11) NOT NULL, `nodename` varchar(200) DEFAULT NULL, `pid` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;-- ------------------------------ Records of treenodes-- ----------------------------INSERT INTO `treenodes` VALUES ('1', 'A', '0');INSERT INTO `treenodes` VALUES ('2', 'B', '1');INSERT INTO `treenodes` VALUES ('3', 'C', '1');INSERT INTO `treenodes` VALUES ('4', 'D', '2');INSERT INTO `treenodes` VALUES ('5', 'E', '2');INSERT INTO `treenodes` VALUES ('6', 'F', '3');INSERT INTO `treenodes` VALUES ('7', 'G', '6');INSERT INTO `treenodes` VALUES ('8', 'H', '0');INSERT INTO `treenodes` VALUES ('9', 'I', '8');INSERT INTO `treenodes` VALUES ('10', 'J', '8');INSERT INTO `treenodes` VALUES ('11', 'K', '8');INSERT INTO `treenodes` VALUES ('12', 'L', '9');INSERT INTO `treenodes` VALUES ('13', 'M', '9');INSERT INTO `treenodes` VALUES ('14', 'N', '12');INSERT INTO `treenodes` VALUES ('15', 'O', '12');INSERT INTO `treenodes` VALUES ('16', 'P', '15');INSERT INTO `treenodes` VALUES ('17', 'Q', '15');-- ------------------------------ Function structure for `getChildList`-- ----------------------------DROP FUNCTION IF EXISTS `getChildList`;CREATE FUNCTION `getChildLst`(rootId INT) RETURNS varchar(1000) BEGIN DECLARE sTemp VARCHAR(1000); DECLARE sTempChd VARCHAR(1000); SET sTemp = '$'; SET sTempChd =cast(rootId as CHAR); WHILE sTempChd is not null DO SET sTemp = concat(sTemp,',',sTempChd); SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0; END WHILE; RETURN sTemp; END -- 查询语句-- select * from treeNodes where FIND_IN_SET(id, getChildLst(4));
0 0
- mysql的逆袭:如何做递归层次查询
- mysql的逆袭:如何做递归层次查询
- mysql的逆袭:如何做oracle的start with connect by prior递归层次查询
- mysql如何做递归层次查询
- mysql做递归层次查询
- mysql如何做递归查询!!
- MySQL递归层次查询
- MySql递归层次查询
- Mysql中的递归层次查询(父子查询)
- Mysql中的递归层次查询(父子查询)
- Mysql中的递归层次查询(父子查询)
- Mysql中的递归层次查询(父子查询)
- 【mysql 递归查询】Mysql中的递归层次查询(父子查询)
- oracle 递归层次查询
- 递归层次汇总查询
- Oracle 层次查询、递归
- MySql的递归树状查询
- mysql 递归查询的方法
- [转]结构体边界对齐问题
- C中方法的返回值可以是多个(Android之JNI)
- XML和JSON
- Linux系统下开发或者运行java程序
- C++ 拷贝构造函数和重载赋值操作符不能相互调用
- mysql的逆袭:如何做递归层次查询
- Nginx与PHP(FastCGI)的安装、配置与优化
- 支持多平台的2D和3D游戏引擎Godot开源
- Weighttp 学习 使用 安装
- rtmp服务器解决方案
- 编译及安装QCA类库及使用
- Android SDK 更新问题解决方法
- ios7版 30天精通iPhone手机编程 第24天 璀璨的烟花
- 算法入门经典第八章学习笔记(中)