SQL如何实现MYSQL的递归查询

众所周知,目前的mysql版本中并不支持直接的递归查询,但是通过递归到迭代转化的思路,还是可以在一句SQL内实现树的递归查询的。这个得益于Mysql允许在SQL语句内使用@变量。以下是示例代码。

创建表格

CREATE TABLE `treenodes` (
  `id` int , -- 节点ID
  `nodename` varchar (60), -- 节点名称
  `pid` int -- 节点父ID
); 

插入测试数据

INSERT INTO `treenodes` (`id`, `nodename`, `pid`) VALUES
('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'),('18','R','3'),
('19','S','2'),('20','T','6'),('21','U','8');

查询语句

 SELECT id AS ID,pid AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM (
   SELECT id,pid,
   @le:= IF (pid = 0 ,0,
     IF( LOCATE( CONCAT('|',pid,':'),@pathlevel)  > 0 ,
         SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1
    ,@le+1) ) levels
   , @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel
   , @pathnodes:= IF( pid =0,',0',
      CONCAT_WS(',',
      IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,
        SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1)
       ,@pathnodes ) ,pid ) )paths
  ,@pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall
    FROM treenodes,
  (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
  ORDER BY pid,id
  ) src
ORDER BY id

最后的结果如下:

ID   父ID    父到子之间级数  父到子路径
------    ------     --------------------    ------------------- 
     1       0              0                     ,0            
     2       1              1                     ,0,1          
     3       1              1                     ,0,1          
     4       2              2                     ,0,1,2        
     5       2              2                     ,0,1,2        
     6       3              2                     ,0,1,3        
     7       6              3                     ,0,1,3,6      
     8       0              0                     ,0            
     9       8              1                     ,0,8          
    10       8             1                     ,0,8          
    11       8             1                     ,0,8          
    12       9             2                     ,0,8,9        
    13       9             2                     ,0,8,9        
    14      12            3                      ,0,8,9,12     
    15      12            3                     ,0,8,9,12     
    16      15            4                     ,0,8,9,12,15  
    17      15            4                     ,0,8,9,12,15  
    18       3            2                      ,0,1,3        
    19       2            2                      ,0,1,2        
    20       6            3                      ,0,1,3,6      
    21       8            1                      ,0,8

以上就是一句SQL实现MYSQL的递归查询的实现全过程,希望对大家的学习有所帮助。

时间: 2015-07-30

如何使用MySQL查询某个列中相同值的数量统计

数据现在是这样的,我想确定出type列中的news和image....甚至以后有其他值,他们分别有多少个. SELECT type, count(1) AS counts FROM material GROUP BY type count(1),代表统计第一列,写上1 比写 *的效率高! 以上所述就是本文的全部内容了,希望大家能够喜欢.

MySQL查询倒数第二条记录实现方法

有时候会用到查询倒数第二条记录 复制代码 代码如下: last=HolderChangeHistory.find_by_sql (["               SELECT * FROM holder_change_histories                   where treasure_id = ?                   order by id desc                     limit   1,1  ",               

MySQL查询和修改auto_increment的方法

本文实例讲述了MySQL查询和修改auto_increment的方法.分享给大家供大家参考.具体如下: 查询表名为tableName的auto_increment值: 复制代码 代码如下: SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name="tableName"; 修改表名为tableName的auto_increment值: 复制代码 代码如下: ALTER TABLE tableName au

数据库表的查询操作实践演练(实验三)

继前两次的实验,本次实验以熟练掌握利用select语句进行各种查询操作:单表查询.多表连接及查询.嵌套查询.集合查询等,巩固数据库查询操作. 下面就跟着小编一起练习吧! 在实验一创建并插入数据的表(Student, Course,SC,Teacher,TC)的基础上,完成以下操作. (1)将教师'罗莉'的名字改为'罗莉莉'. 复制代码 代码如下: update Teacher set tname='罗莉莉' where tname='罗莉' (2)将两个同学(数据自己临时设置,用后即删除)的两门

mysql查询今天、昨天、近7天、近30天、本月、上一月的SQL语句

mysql查询今天,昨天,近7天,近30天,本月,上一月数据的方法分析总结: 话说有一文章表article,存储文章的添加文章的时间是add_time字段,该字段为int(5)类型的,现需要查询今天添加的文章总数并且按照时间从大到小排序,则查询语句如下: 复制代码 代码如下: select * from `article` where date_format(from_UNIXTIME(`add_time`),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')

sql查询出各科成绩最好的学生信息

1.相关数据表 Score表  [User]表 SQL语句如下: 复制代码 代码如下: --查询出各科成绩最好的学生信息 --自连接 --SELECT TOP 1 * FROM Score B WHERE B.ScoreName = '数学' ORDER BY B.Score DESC SELECT A.ID,U.Name,A.ScoreName,A.Score FROM Score A,[User]U WHERE UID IN (SELECT TOP 1 UID FROM Score B WH

一个优化MySQL查询操作的具体案例分析

问题描述 一个用户反映先线一个SQL语句执行时间慢得无法接受.SQL语句看上去很简单(本文描述中修改了表名和字段名): SELECT count(*) FROM a JOIN b ON a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ; 且查询需要的字段都建了索引,表结构如下: CREATE TABLE `a` ( `L` timestamp NOT NULL DEFAULT

MySql查询时间段的方法

本文实例讲述了MySql查询时间段的方法.分享给大家供大家参考.具体方法如下: MySql查询时间段的方法未必人人都会,下面为您介绍两种MySql查询时间段的方法,供大家参考. MySql的时间字段有date.time.datetime.timestamp等,往往我们在存储数据的时候将整个时间存在一个字段中,采用datetime类型:也可能采用将日期和时间分离,即一个字段存储date,一个字段存储时间time.无论怎么存储,在实际应用中,很可能会出现包含"时间段"类型的查询,比如一个访

SQL查询出表、存储过程、触发器的创建时间和最后修改时间示例

--查询建立时间 --表 select * from sysobjects where id=object_id(N'表名') and xtype='U' --表的结构 select * from syscolumns where id=object_id(N'表名') --存储过程 select * from sysobjects where id=object_id(N'dqtx') and xtype='P' --查询最后修改时间 --存储过程 select name,modify_dat

SQL大量数据查询的优化及非用like不可时的处理方案

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放

数据库表的查询操作(实验二)

[实验目的]:了解SQL语言的使用,进一步理解关系运算,巩固数据库的基础知识. [实验要求]:掌握利用Select语句进行各种查询操作:单表查询.多表连接及查询.嵌套查询.集合查询等. [实验内容] 一.单表查询 1.简单查询 打开查询分析器,根建立teacher表,并加入数据.从teacher表中分别检索出教师的所有信息,以及仅查询教工号.姓名和职称.语句如下: select * from teacher select tno, tname from teacher 如要查询时改变列标题的显示

mysql查询昨天 一周前 一月前 一年前的数据

mysql 昨天 一周前 一月前 一年前的数据 这里主要用到了DATE_SUB, 参考如下 复制代码 代码如下: SELECT * FROM yh_contentwhere inputtime>DATE_SUB(CURDATE(), INTERVAL 1 DAY)where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH)where inp

大幅优化MySQL查询性能的奇技淫巧

回顾 MySQL / InnoDB 的改善历史.你能很容易发现.在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速,它很容易搞懂,以及在read-only(RO)有着良好的扩张性.也很期待它在read+write(RW)上达到一个较高水平.(特别是在读取数据是数据库主要工作的时候) 然而.我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中,主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望.但是RW依赖RO下.

50条SQL查询技巧、查询语句示例

Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表   问题: 1.查询"001"课程比"002"课程成绩高的所有学生的学号: 复制代码 代码如下: select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC whe

单个select语句实现MySQL查询统计次数

单个select语句实现MySQL查询统计次数 单个select语句实现MySQL查询统计次数的方法用处在哪里呢?用处太多了,比如一个成绩单,你要查询及格得人数与不及格的人数,怎么一次查询出来?MySQL查询统计次数简单的语句肯定是这样了: 复制代码 代码如下: select a.name,count_neg,count_plus from    (select count(id) as count_plus,name from score2 where score >=60 group by

MySQL学习笔记3:表的基本操作介绍

要操作表首先需要选定数据库,因为表是存在于数据库内的 选择数据库 mysql> use school; Database changed 选择好数据库之后,我们就可以在此数据库之中创建表了 创建表 mysql> create table student( -> id int, -> name varchar(20), -> sex boolean -> ); Query OK, 0 rows affected (0.11 sec) create table用于创建表,后

mysql学习笔记之表的基本操作

创建表 create table 表名 create table if not exists 表名 mysql> create database company; Query OK, 1 row affected (0.00 sec) mysql> use company; Database changed mysql> create table if not exists t_dept( -> deptno int, -> dname varchar(20), ->

Flask框架学习笔记之表单基础介绍与表单提交方式

本文实例讲述了Flask框架学习笔记之表单基础介绍与表单提交方式.分享给大家供大家参考,具体如下: 表单介绍 表单是HTML页面中负责数据采集功能的部件.由表单标签,表单域和表单按钮组成.通过表单,将用户输入的数据提交给服务器,并交给服务端进行处理. 表单标签 用于声明表单的范围,位于表单标签的元素将被提交. 语法:<form></form> 属性:Method规定用于发送表单数据的 HTTP 方法. Enctype规定在向服务器发送表单数据之前如何对其进行编码.(适用于 meth

MySQL学习笔记之创建、删除、修改表的方法

本文实例讲述了MySQL学习笔记之创建.删除.修改表的方法.分享给大家供大家参考,具体如下: 创建表: create table users( id int, name varchar(64), sex bit(1), birthday date, Entry_date date, job varchar(32), salary float, resume text ); 1 添加列: alter table 表名 add 列名 数据类型 alter table users add image

MySQL学习笔记之数据定义表约束,分页方法总结

本文实例讲述了MySQL学习笔记之数据定义表约束,分页方法.分享给大家供大家参考,具体如下: 1. primary key 主键 特点:主键是用于唯一标识一条记录的约束,一张表最多只能有一个主键,不能为空也不能重复 create table user1(id int primary key,name varchar(32)); mysql> insert into user1 values(1,'hb'); Query OK, 1 row affected (0.10 sec) mysql>

MySQL学习笔记之数据的增、删、改实现方法

本文实例讲述了MySQL学习笔记之数据的增.删.改实现方法.分享给大家供大家参考,具体如下: 一.增加数据 插入代码格式: insert into 表明 [列名-] values (值-) create table test21(name varchar(32)); insert into test21 (name) values ('huangbiao'); 插入原则: 1.插入的数据应与字段的数据类型相同 2.数据的大小应该在列的规定范围内 3.在values中列出的数据位置必须与被加入的列

微信小程序学习笔记之表单提交与PHP后台数据交互处理图文详解

本文实例讲述了微信小程序学习笔记之表单提交与PHP后台数据交互处理.分享给大家供大家参考,具体如下: 前面一篇结介绍了微信小程序函数定义.页面渲染.这里介绍form表单提交与后台php数据交互处理. [form表单提交] form.wxml: <form bindsubmit="formSubmit" bindreset="formReset"> <view> 昵称:<input type="text" name=&

AngularJS学习笔记之表单验证功能实例详解

本文实例讲述了AngularJS学习笔记之表单验证功能.分享给大家供大家参考,具体如下: 一.执行基本的表单验证 <!DOCTYPE html> <html ng-app='exampleApp'> <head> <meta charset="UTF-8"> <title>表单</title> <script src="../../js/angular.min.js" type="

Symfony2框架学习笔记之表单用法详解

本文实例讲述了Symfony2框架表单用法.分享给大家供大家参考,具体如下: 对于一个Web开发者来说,处理HTML表单是一个最为普通又具挑战的任务.Symfony2集成了一个Form组件,让处理表单变的容易起来.在这一节里,我们将 从基础开始创建一个复杂的表单,学习表单类库中最重要的内容. Symfony2 的Form组件是一个独立的类库,你可以在Symfony2项目之外使用它. 创建一个简单的表单: 假设你要创建一个应用程序的todo列表,需要显示一些任务.因为你的用户需要编辑和创建任务,所

一千行的MySQL学习笔记汇总

本文详细汇总了MySQL学习中的各类技巧,分享给大家供大家参考. 具体如下: /* 启动MySQL */ net start mysql /* 连接与断开服务器 */ mysql -h 地址 -P 端口 -u 用户名 -p 密码 /* 跳过权限验证登录MySQL */ mysqld --skip-grant-tables -- 修改root密码 密码加密函数password() update mysql.user set password=password('root'); SHOW PROCE