oracle数据库设计思维导图,Oracle

WITH子查询也称为CTE (Common Table Expression)，是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询，把它被称作SUBQUERY FACTORING(分解子查询)。

WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面，只能够引用一次；而WITH子查询需要在引用之前先定义，一旦定义了在整个查询的后续部分就可以按名称来反复引用，从这点来看又很像临时表。

从版本11GR2开始，ORACLE支持递归的WITH, 即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事，其他数据库如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说，这一递归特性还是很令人期待的，利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性，并把它和以往的实现手段(主要是CONNECT BY层次查询)作比较。

我们先来看看这个递归WITH子查询的语法：

WITH

① query_name ([c_alias [, c_alias]...])

② AS (subquery)

③ [search_clause]

④ [cycle_clause]

⑤ [,query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...

①这是子查询的名称，和以往不同的是，必须在括号中把这个子查询的所有列名写出来。

②AS后面的subquery就是查询语句，递归部分就写在这里。

③遍历顺序子句，可以指定深度优先或广度优先遍历顺序。

④循环子句，用于中止遍历中出现的死循环。

⑤如果还有其他递归子查询，定义同上。

subquery部分由两个成员组成：anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来，anchor member 必须写在recursive member前面。

anchor member用来定位递归的入口，锚点成员是一个SELECT语句，它不可以包含自身名称(query_name)。这相当于CONNECT BY查询中的START WITH，典型写法就是:

SELECT ... FROM 要遍历的表 WHERE ... (起始条件)

递归成员也是一个SELECT语句，用于定义上下级的关系，它必须包含自身名称(即query_name)，而且仅仅只能引用一次。递归正是体现在对于自身的引用。典型的做法就是把query_name和其他表(一般来说就是你要遍历的表)做一个连接，连接条件表明了上下级的关系。必须注意，在这个query_name中，并不是截止目前为止的所有数据都是可见的，可见的只是上次递归新加入的最近的一层数据。对query_name列的引用相当于CONNECT BY中的PRIOR操作符。当找不到满足条件的下级，遍历就会停止；如果你还有其他的递归出口条件，也可以一起写在WHERE中，当WHERE不满足时，遍历就会停止，这就是在遍历树、图时候的剪枝操作。越早停止则效率越高。

这个递归成员就是程序员发挥创造力的地方，以往在CONNECT BY中做不到的事情，比如沿路径求和、求积等运算，现在都轻而易举。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'来实现。

搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。

一个例子，从scott/tiger的emp表来查找上下级关系：

WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必须把结构写出来

SELECT empno, ename, job, mgr, deptno ---- 先写锚点查询，用START WITH的条件

,1 AS the_level ---- 递归起点，第一层

,'\'||ename ---- 路径的第一截

,ename AS top_manager ---- 原来的CONNECT_BY_ROOT

FROM EMP

WHERE mgr IS NULL ---- 原来的START WITH条件

UNION ALL ---- 下面是递归部分

SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一层数据，来自要遍历的emp表

,1 + t.the_level ---- 递归层次，在原来的基础上加1。这相当于CONNECT BY查询中的LEVEL伪列

,t.path||'\'||e.ename ---- 把新的一截路径拼上去

,t.top_manager ---- 直接继承原来的数据，因为每个路径的根节点只有一个

FROM t, emp e ---- 典型写法，把子查询本身和要遍历的表作一个连接

WHERE t.empno = e.mgr ---- 原来的CONNECT BY条件

) ---- WITH定义结束

SELECT * FROM T

--正序

WITH rs

AS (SELECT COLNAME name, row_number () OVER () RN

FROM T_CS_WJDR_DRDYMX

WHERE WJLXDM ='0044'

ORDERBY SXH DESC),

RPL (RN, name)

AS (SELECT ROOT.RN, CAST (ROOT.name ASVARCHAR (2000))

FROM rs ROOT

UNIONALL

SELECT CHILD.RN, CHILD.name ||','|| PARENT.name

FROM RPL PARENT, rs CHILD

WHERE PARENT.RN -1= CHILD.RN)

SELECTMAX (name) name

FROM RPL

GROUPBY RN

ORDERBY RN

FETCH FIRST 1 ROWS ONLY;

--反序

WITH rs

AS (SELECT COLNAME name, row_number () OVER () RN

FROM T_CS_WJDR_DRDYMX

WHERE WJLXDM ='0044'

ORDERBY SXH DESC),

RPL (RN, name)

AS (SELECT ROOT.RN, CAST (ROOT.name ASVARCHAR (2000))

FROM rs ROOT

UNIONALL

SELECT CHILD.RN, CHILD.name ||','|| PARENT.name

FROM RPL PARENT, rs CHILD

WHERE PARENT.RN +1= CHILD.RN)

SELECTMAX (name) name

FROM RPL

GROUPBY RN

ORDERBY RN desc

FETCH FIRST 1 ROWS ONLY;

例子-自由

create table emp_tag(id number primary key,empno NUMBER(4) ,tag varchar2(200));

insert into emp_tag (id, empno, tag) values (1, 7876, '港股通');

insert into emp_tag (id, empno, tag) values (2, 7876, '大消费');

insert into emp_tag (id, empno, tag) values (3, 7876, '大健康');

insert into emp_tag (id, empno, tag) values (4, 7902, '一带一路');

insert into emp_tag (id, empno, tag) values (5, 7902, '食品饮料');

insert into emp_tag (id, empno, tag) values (6, 7902, '农林木业');

insert into emp_tag (id, empno, tag) values (7, 7369, '工业4.0');

insert into emp_tag (id, empno, tag) values (8, 7369, '信息设备');

insert into emp_tag (id, empno, tag) values (9, 7369, '煤炭石油');

insert into emp_tag (id, empno, tag) values (10, 7499, '国企改革');

insert into emp_tag (id, empno, tag) values (11, 7499, '医药');

insert into emp_tag (id, empno, tag) values (12, 7499, '中小盘');

commit;

WITH rs AS

(SELECT EMPNO, TAG name, row_number() OVER(ORDER BY ID) RN FROM emp_tag),

RPL(EMPNO,

RN,

name)AS

(SELECT EMPNO, ROOT.RN,CAST(ROOT.name AS VARCHAR(2000))

FROM rs ROOT

UNION ALL

SELECT CHILD.EMPNO, CHILD.RN, CHILD.name ||','|| PARENT.name

FROM RPL PARENT, rs CHILD

WHERE PARENT.RN -1= CHILD.RN

AND PARENT.EMPNO = CHILD.EMPNO)

SELECT EMPNO,NAME

FROM(SELECT EMPNO,

NAME,

ROW_NUMBER() OVER(PARTITION BY EMPNO ORDER BY LENGTH(NAME) DESC) RN_LENG

FROM RPL) T

WHERE T.RN_LENG ='1';

结果：