connect by 层次查询子句,可用于递归树查询和层次查询
递归查询结构:start with expression1 connect by expression2
expression1:根节点的限制条件。
expression2:递 归关联条 件 prior 后跟的是母节点,表示上一条记录。
例子:
expression2:递 归关联条 件 prior 后跟的是母节点,表示上一条记录。
例子:
START
WITH o.COMCODE =
'1'
CONNECT BY NOCYCLE o.UPPERCOMCODE
=
PRIOR o.COMCODE
CONNECT BY NOCYCLE o.UPPERCOMCODE
=
PRIOR o.COMCODE
应用序列实现循环:connect by expression
expression: 类似rownum<=10,作为循环条件。
例子:
实例一结果如下:
例子:
--实例一
select rownum from dual connect by 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
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
-- 如统计期间为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
FROM employee
转载于:https://blog.51cto.com/atip3/1100024