原创

Oracle Sql 语句递归查询示例

        递归调用是一种特殊的嵌套调用,是某个函数调用自己,而不是另外一个函数。递归调用一种解决方案,一种是逻辑思想,将一个大工作分为逐渐减小的小工作,下面是Sql语句递归方式的示例。


创建表(主外键暂未考虑)

CREATE TABLE CONURBATION
(
  ID    
    NUMBER(10)  
     
     
   NOT NULL,
  PARENT_ID
 NUMBER(10),
  NAME    
  VARCHAR2(255 BYTE)  
     
 NOT NULL
);

插入相关数据,方便下文进行操作

INSERT INTO CONURBATION(ID,NAME) VALUES(1,'河北省');
INSERT INTO CONURBATION(ID,PARENT_ID,NAME)VALUES(2,1,'石家庄市');
INSERT INTO CONURBATION(ID,PARENT_ID,NAME)VALUES(3,1,'唐山市');
INSERT INTO CONURBATION(ID,PARENT_ID,NAME)VALUES(4,2,'高邑县');
INSERT INTO CONURBATION(ID,PARENT_ID,NAME)VALUES(5,2,'藁城市');
INSERT INTO CONURBATION(ID,PARENT_ID,NAME)VALUES(6,3,'丰南区');
INSERT INTO CONURBATION(ID,PARENT_ID,NAME)VALUES(7,3,'丰润区');

查询数据SELECT * FROM CONURBATION结果如下:

1 1 河北省

2 2 1 石家庄市

3 3 1 唐山市

4 4 2 高邑县

5 5 2 藁城市

6 6 3 丰南区

7 7 3 丰润区


SYS_CONNECT_BY_PATH函数是oracle9i提出来的,并且它必须与connect by 联用;其中第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符。

SELECT NAME,
     
 PARENT_ID,
     
 SUBSTR(SYS_CONNECT_BY_PATH(NAME,
'->'), 3) PATH_NAME
  FROM CONURBATION
 START WITH NAME = '唐山市'
CONNECT BY PRIOR ID = PARENT_ID

数据如下:

1 唐山市 1 唐山市

2 丰南区 3 唐山市->丰南区

3 丰润区 3 唐山市->丰润区



START WITH 代表你要开始遍历的的节点,CONNECT BY PRIOR 是标示父子关系的对应。无论正树还是倒树, 关键就在于connect by的条件. 

正树:  必须是  ‘父’= prior ‘子’

倒树:  必须是  ‘子’= prior ‘父’

(1)

SELECT *
  FROM CONURBATION
 START WITH NAME = '唐山市'
CONNECT BY PRIOR ID = PARENT_ID

1 3 1 唐山市

2 6 3 丰南区

3 7 3 丰润区

(2)

SELECT *
  FROM CONURBATION
 START WITH NAME = '丰润区'
CONNECT BY PRIOR PARENT_ID = ID

1 7 3 丰润区

2 3 1 唐山市

3 1 河北省


where条件的使用,过滤掉不符合条件的数据,SQL语句如下:

SELECT NAME,
     
 PARENT_ID,
     
 SUBSTR(SYS_CONNECT_BY_PATH(NAME,
'->'), 3) PATH_NAME
  FROM CONURBATION
 WHERE NAME <>
'唐山市'
 START WITH NAME = '河北省'
CONNECT BY PRIOR ID = PARENT_ID

数据如下:

1 河北省 河北省

2 石家庄市 1 河北省->石家庄市

3 高邑县 2 河北省->石家庄市->高邑县

4 藁城市 2 河北省->石家庄市->藁城市

5 丰南区 3 河北省->唐山市->丰南区

6 丰润区 3 河北省->唐山市->丰润区

结果:只实现了单一的去除,去除了唐山市的数据但是子集数据还会显示。


树型结构的条件(实现分支截掉)

过滤采用树型结构, 如果将树上的某一分支截掉,将分支后面的结构都抛弃掉,而不显示。当前不能采用where,where条件只能去除单一的不符合要求数据。此时,树型的过滤条件加在connect by后面即可解决。

SELECT NAME,
     
 PARENT_ID,
     
 SUBSTR(SYS_CONNECT_BY_PATH(NAME,
'->'), 3) PATH_NAME
  FROM CONURBATION
 START WITH NAME = '河北省'
CONNECT BY PRIOR ID = PARENT_ID
     
 and name <>
'唐山市'

数据如下:

1 河北省 河北省

2 石家庄市 1 河北省->石家庄市

3 高邑县 2 河北省->石家庄市->高邑县

4 藁城市 2 河北省->石家庄市->藁城市



按时间顺序排序,SQL语句如下:

SELECT *
  FROM TBCOMMENT TB
 WHERE TB.COURSEID =
'D22B987E39CC11E281E2CF41FD03726B'
 START WITH TB.PARENTID = '0'
CONNECT BY PRIOR TB.ID = TB.PARENTID
 ORDER SIBLINGS BY TB.CREATEDATE DESC

数据如下:
1    d22b987e39cc11e281e2cf41fd03726b    0                                   2013-06-07 09:16:19
2    d22b987e39cc11e281e2cf41fd03726b    0                                   2013-06-06 17:36:55
3    d22b987e39cc11e281e2cf41fd03726b    0                                   2013-06-03 15:41:14
4    d22b987e39cc11e281e2cf41fd03726b    4cf39d71cdb311e2901301e3fed287e6    2013-06-05 15:56:36
5    d22b987e39cc11e281e2cf41fd03726b    7218d22ecdb511e2bb32c3efbbda21e6    2013-06-05 15:56:36
6    d22b987e39cc11e281e2cf41fd03726b    7272d911cdb511e2bb32c3efbbda21e6    2013-06-06 17:37:38
7    d22b987e39cc11e281e2cf41fd03726b    7272d911cdb511e2bb32c3efbbda21e6    2013-06-06 17:36:17
8    d22b987e39cc11e281e2cf41fd03726b    896afec1ce8c11e2b534f5390ef37c16    2013-06-07 10:14:33
9    d22b987e39cc11e281e2cf41fd03726b    0                                   2013-06-02 15:40:55
10   d22b987e39cc11e281e2cf41fd03726b    414d6ff0cdb311e2901301e3fed287e6    2013-06-0 15:56:35
11   d22b987e39cc11e281e2cf41fd03726b    71ec6afdcdb511e2bb32c3efbbda21e6    2013-06-05 15:56:36
12   d22b987e39cc11e281e2cf41fd03726b    0                                   2013-06-01 15:41:19
13   d22b987e39cc11e281e2cf41fd03726b    4f9c9d62cdb311e2901301e3fed287e6    2013-06-05 15:56:30
14    d22b987e39cc11e281e2cf41fd03726b    6ebcaedccdb511e2bb32c3efbbda21e6    2013-06-05 15:56:36

关注下方微信公众号“Java精选”(w_z90110),回复关键字领取资料:如HadoopDubboCAS源码等等,免费领取资料视频和项目。 

涵盖:程序人生、搞笑视频、算法与数据结构、黑客技术与网络安全、前端开发、Java、Python、Redis缓存、Spring源码、各大主流框架、Web开发、大数据技术、Storm、Hadoop、MapReduce、Spark、elasticsearch、单点登录统一认证、分布式框架、集群、安卓开发、iOS开发、C/C++、.NET、Linux、Mysql、Oracle、NoSQL非关系型数据库、运维等。

评论

分享:

支付宝

微信