• connect by 层次查询子句，可用于递归树查询和层次查询

  • 递归查询结构：start with expression1 connect by  expression2

expression1:根节点的限制条件。
expression2：递归关联条件 prior 后跟的是母节点，表示上一条记录。

例子：
START WITH o.COMCODE ='1'
      CONNECT BY NOCYCLE o.UPPERCOMCODE
      =
      PRIOR o.COMCODE

  • 应用序列实现循环：connect by expression

expression: 类似rownum<=10，作为循环条件。
例子：
--实例一
select rownum from dual connect by rownum<    10
实例一结果如下：
       ROWNUM
1        1
2        2
3        3
4        4
5        5
6        6
7        7
8        8
9        9

--实例二
--如统计期间为2009-1-1到2011-12-31，则其中“上年核保起期”、“上年核保止期”、“第二年注销退保起期”、“第二年注销退保止期”为“2009-10-1”、“2009-12-31”、“2010-1-1”、“2010-3-1” || “2010-10-1”、“2010-12-31”、“2011-1-1”、“2011-3-1”。也就是如此循环，直到下一轮次的“第二年注销退保止期”年度不晚于“”监测期间止所在的年度。

SELECT T.*
    FROM t_audit_inflated_premium T,
    (SELECT TO_CHAR(ADD_MONTHS(#monitorDateStart#, 12 * (ROWNUM - 1)),
                                             'yyyy') AS STATISTICYEAR
                    FROM DUAL
                CONNECT BY ROWNUM <![CDATA[<]]>
                                     (SELECT MONTHS_BETWEEN(TRUNC(#monitorDateEnd#, 'yyyy'),
                                                                                    TRUNC(#monitorDateStart#, 'yyyy'))
                                            FROM DUAL) / 12 + 1) T2
                WHERE TO_CHAR(T.UNDERWRITEENDDATE, 'yyyy') = T2.STATISTICYEAR
                    AND T.UNDERWRITEENDDATE BETWEEN
                            TO_DATE(TO_CHAR(T.UNDERWRITEENDDATE, 'yyyy') || '10-1', 'yyyy-mm-dd') AND
                            TO_DATE(TO_CHAR(T.UNDERWRITEENDDATE, 'yyyy') || '12-31',
                             'yyyy-mm-dd')
                    AND T.EUNDERWRITEENDDATE BETWEEN
                            TO_DATE(TO_CHAR(T.UNDERWRITEENDDATE, 'yyyy') + 1 || '01-1',
                             'yyyy-mm-dd') AND
                            TO_DATE(TO_CHAR(T.UNDERWRITEENDDATE, 'yyyy') + 1 || '03-1',
                             'yyyy-mm-dd')
                    AND TO_CHAR(T.EUNDERWRITEENDDATE, 'yyyy') =
                            TO_CHAR(T.UNDERWRITEENDDATE, 'yyyy') + 1

  • row_number() OVER  排序

结构：row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 

表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)


SELECT *, Row_Number() OVER(partition by deptid ORDER BY salary desc) rank
    FROM employee