Oracle树形结构递归查询

一、树形查询

/**
 * 递归查询树形结构==================================================
 */
@PostMapping("/recursive")
@ResponseBody
public List<Map<String, Object>> findWorld() {
    List<Map<String, Object>> allList = new ArrayList<>();      //接收所有的信息
    List<Map<String, Object>> parentMapList = new ArrayList<>();    //接收获取的父节点
    List<Map<String, Object>> retList = new ArrayList<>();    //接收获取的父节点
    try {
        allList = myTestService.findWorld();           //获取所有的信息
        for (int i = 0; i < allList.size {          //循环所有信息找出所有的根节点
            if ("-1".equals(allList.get.get.toString {
                parentMapList.add(allList.get;         //将找出来的根节点重新存到一个List<Map>集合中
                allList.remove;            //从所有的数据中移除根节点
                i--;                          //每移除一个根节点就将所有数据的个数减一
            }
        }
        retList = recursive(allList,parentMapList);  //调用此方法寻找子节点
    } catch (Exception e) {
        e.printStackTrace();
    }
    return retList;
}

/**
 * @param parentMapList   所有父节点
 * @param allList         所有数据
 * @return
 */
public List<Map<String, Object>> recursive(List<Map<String, Object>> allList,List<Map<String,Object>> parentMapList) {
      //循环根节点
        for(int j = 0;j<parentMapList.size{
            List<Map<String, Object>> tempList = new ArrayList<>();  //用来存取子节点
            //循环全部节点,判断节点中P_id是否与根节点中的id相同
            for(int k = 0;k<allList.size{
                if(allList.get.get.toString().equals(parentMapList.get.get.toString{
                  tempList.add(allList.get;
                }
            }
            if(tempList.size{
                parentMapList.get.put("children",tempList);
                recursive(allList,tempList);   //此次循环又将tempList作为parentMapList(父节点去找其他的子节点),直到没有子节点在执行j+1
            }
        }
    return  parentMapList;
}

        
最近简单的对oracle,mysql,sqlserver2005的数据分页查询作了研究,把各自的查询的语句贴出来供大家学习…..

简述

    简单概括一下Oracle,MySql,SQL
Sqlserver这三个数据库的分页查询语句。


 

在Oracle中,对于树形查询可以使用start with … connect by

select *
  from CX_AWK_PDT_CATEGORY
 START WITH ROW_ID = '8a8293b6574685440157469706210002'
CONNECT BY PRIOR ROW_ID = PARENT_ID

       (一)、** mysql的分页查询**

Oracle分页查询

    例:每页显示两条数据,现在要查询第二页,也就是第3-4条数据。

    查询语句:

1 select * from (
2        select dept.*,rownum num from dept where rownum <= 4
3 ) d where d.num >= 3

 

    解释:使用rownum列进行分页,子查询中设置查询结束行,父查询中设置查询起始行。

    注意:子查询中的rownum列要起一个别名。

select * from treeTablestart with id='1' connect by id = prior parent_id;

  

        mysql的分页查询是最简单的,借助关键字limit即可实现查询,查询语句通式:

MySQL分页查询

    例:每页显示10条数据,现在要查询第三页,也就是第21-30条数据。

    查询语句:

1 SELECT * FROM `tab_sys_menu` LIMIT 20, 10

    解释:使用limit进行分页查询,limit之后的第一个参数是设置查询起始行,第二个参数是设置查询行数(也就是每页显示数量)。

    注意:起始行从0开始。

若将一个树状结构存储在一张表里,需要在表中存入两个字段ID和PARENTID,表示每一条记录的parent是谁。

/*

* sql:可以是单表的查询语句,也可以是多表的联合查询语句

* firstIndex:其实的索引

* pageSize:每页显示的记录数

*/

select o.* from (sql) o limit firstIndex,pageSize

SQL Server分页查询

    例:每页显示10条数据,现在要查询第三页,也就是第21-30条数据。

    查询语句:

1 select top 10 * from Room where RoomId not in (
2     select top 20 RoomId from Room
3 )

    解释:使用子查询进行分页,子查询里面的top值设置为要排除的行,比如要从第21条数据开始查询,那么前20条数据肯定是不要的,再比如要从第41条数据开始查询,那么前40条数据肯定是不要的。

          父查询的top值设置为查询行数(也就是每页显示数量)。


 

table: treeTable

如下面的截图,每页显示的记录数为20:

扩展

    其实分页查询语句也不一定非得自己写,如果项目dao层用hibernate实现的话,有两种方式可以非常方便的实现分页查询。

    它们分别是Criteria查询和HQL查询。

    Criteria查询:创建Criteria对象之后,有两个方法,分别是:setMaxResults(设置每页显示记录数)和setFirstResult(设置从第几行开始查询)。

    HQL查询:其实和上面的一样,根据Session创建Query之后,query对象也有setMaxResults(设置每页显示记录数)和setFirstResult(设置从第几行开始查询)方法。

    例:session.createQuery(hql).setFirstResult((result.getPageNo()-1)*result.getPageSize()).setMaxResults(size).list();

图片 1

                                                
 查询(1-20)这20条记录
图片 2

1.从根节点遍历子节点.:

                                             查询(21-40)这20条记录

select * from treeTable start with id=1 connect by prior id=parentid (prior
表示上一条记录)

图片 3

2.从一个叶子追溯到根节点:

        mysql的分页查询就这么简单……

select * fromtreeTable start with id=3 connect by prior parentid=id (prior
表示上一条记录)

  (二)、sqlserver2005的分页查询

3.可通过level 关键字查询所在层次.

   
在sqlserver2005之前一直借助top关键字来实现分页查询,不过效率低,在sqlserver2005及其之后的版本都使用row_number()解析函数来完成分页查询,效率有了很大的提高,不过sql语句比较复杂,下面给出分页查询的通式:

select *,levelfrom treeTable start with id=1 connect by prior id=parentid (prior
表示上一条记录)

/*

* firstIndex:起始索引


* pageSize:每页显示的数量

* orderColumn:排序的字段名

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

*/

select top pageSize o.* from (select row_number() over(order by orderColumn) as rownumber,* from(sql) as o where rownumber>firstIndex;

MySql树形结构递归查询

 下面看截图,每页显示20条记录数:

mysq虽没有自带的语法支持,不过可以通过创建函数来实现递归查询。

                                                           
 查询(1-20)这20条记录

如下图所示。。。

图片 4

图片 5

                                                        
查询(21-40)这20条记录

直接上sql语句

图片 6

create table `nodelist` (    `id` int ,    `nodecontent` varchar ,    `pid` int ; insert into `nodelist` (`id`, `nodecontent`, `pid`) values('1','a',NULL);insert into `nodelist` (`id`, `nodecontent`, `pid`) values('2','b','1');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('3','c','1');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('4','d','2');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('5','e','3');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('6','f','3');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('7','g','5');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('8','h','7');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('9','i','8');insert into `nodelist` (`id`, `nodecontent`, `pid`) values('10','j','8');

    知道了sqlserver中的row_number函数,分页也就简单了…..

之后创建一个函数

  (三)、oracle分页查询

DROP FUNCTION IF EXISTS `getChild`$$CREATE DEFINER=`root`@`localhost` FUNCTION `getChild`(rootId INT) RETURNS VARCHAR CHARSET utf8BEGIN        DECLARE ptemp VARCHAR;        DECLARE ctemp VARCHAR;               SET ptemp = '#';               SET ctemp =CAST(rootId AS CHAR);               WHILE ctemp IS NOT NULL DO                 SET ptemp = CONCAT(ptemp,',',ctemp);                SELECT GROUP_CONCAT INTO ctemp FROM nodelist                   WHERE FIND_IN_SET(pid,ctemp)>0;                END WHILE;                 RETURN ptemp;      END$$DELIMITER ;

   
接下来重点说说oracle的分页查询,oracle的分页查询方法相对来说要多点,ROWNUM、row_number(),今天主要将两种效率稍好的分页查询语句。

OK,查询可以通过将函数当做一个查询条件。

    ①ROWNUM查询分页通式:

SELECT * FROM nodelist WHERE FIND_IN_SET(id, getChild
/*

* firstIndex:起始索引

* pageSize:每页显示的数量

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

*/
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex

图片 7

以下截图是以这种方式进行的查询语句:

借鉴

                                                          查询(1-21)这20条记录*****(没有ID=6的记录,所以查询到的最大ID为21)
图片 8

里面用到的内置函数

                                                    
  查询(22-41)这20条记录*****(没有ID=6的记录,所以开始查询到的ID为22,以及最大ID为41)

图片 9

 

    ②row_number()解析函数分页查询通式:

 /*

 * firstIndex:起始索引

 * pageSize:每页显示的数量

 * orderColumn:排序的字段名

 * sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

 */
select * from(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p where p.rownumber>firstIndex) where rownum<=pageSize

 以下截图是使用row_number()方式的分页查询效果:

                                                         
查询(1-21)这20条记录*****(没有ID=6的记录,所以查询到的最大ID为21)
图片 10

                                                   
查询(22-41)这20条记录*****(没有ID=6的记录,所以开始查询到的ID为22,以及最大ID为41)

图片 11

      对于oracle的分页查询,特地选出这两种实现方式是因为这两者各有千秋

     首先, 我们知道在ROWNUM查询的方式中,在第二层的sql语句中有个”where
ROWNUM<firstIndex+pageSize”,根据oracle的原则,第二层查询语句会嵌入到最内层中进行查询,也就是说,最开始执行的查询语句类似于:select
* from wyuse where rownum<(firstIndex+pageSize) order by id
asc,从数据表中查询出(firstIndex+pageSize)条记录,所以如果这个值很小的话,效率会很好,如果对于大数据量的表单,这个值如果是上千,比如:select
* from wyuse where rownum<(5000) order by id
asc,这样一开始会选出5000条记录,效率自然会慢很多….

    
不过,相对于ROWNUM,row_number()方式可能通过简化可以少一层嵌套,不过貌似对于大数量的查询,效率也高不到哪里去…..不过,对于大数量如果为表建立索引再结合row_number()效果会很好(未测试)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图