CONNECT BY应用

一. 等分时间段

    将一天得时间等分，每15分钟一条记录。

SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 15 / 1440, 'hh24:mi:ss')
  FROM (SELECT TO_DATE('00:00:00', 'hh24:mi:ss') S_DT,
               TO_DATE('23:59:59', 'hh24:mi:ss') E_DT
          FROM DUAL) T
CONNECT BY S_DT + (ROWNUM - 1) * 15 / 1440 <= E_DT;

二. 遍历日期

    获取一个月中每一天的日期记录。

 SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE
  FROM (SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 SDATE,
               LAST_DAY(SYSDATE) EDATE
          FROM DUAL) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE

   获取一年中每个月的日期记录

SELECT TO_CHAR(add_months(SDATE ,(ROWNUM - 1)), 'yyyy-mm') DDATE
   FROM (SELECT to_date(to_char(SYSDATE,'yyyy') || '-01','yyyy-mm') SDATE,
                to_date(to_char(SYSDATE,'yyyy') || '-12','yyyy-mm') EDATE
           FROM DUAL) T
 CONNECT BY add_months(SDATE ,(ROWNUM - 1)) <= EDATE;

三. 进制转换

    十六进制转十进制

    SELECT SUM(DATA)
      FROM (SELECT (CASE upper(substr('A123FD32', rownum, 1))
                     WHEN 'A' THEN '10'
                     WHEN 'B' THEN '11'
                     WHEN 'C' THEN '12'
                     WHEN 'D' THEN '13'
                     WHEN 'E' THEN '14'
                     WHEN 'F' THEN '15'
                     ELSE substr('A123FD32', rownum, 1)
                   END) * power(16, length('A123FD32') - rownum) DATA
              FROM dual
            CONNECT BY rownum <= length('A123FD32'));

 

四. 生成序列

    SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 25

 

五.分级查询

    部门分级查询

WITH dep AS(
SELECT 6 DEPID,'项目测试部' DEPNAME,2 UPPERDEPID FROM dual UNION ALL
SELECT 0, '总经办', null FROM dual UNION ALL
SELECT 1, '开发部', 0 FROM dual UNION ALL
SELECT 2, '测试部', 0 FROM dual UNION ALL
SELECT 3, 'Sever开发部', 1 FROM dual UNION ALL
SELECT 4, 'Client开发部', 1 FROM dual UNION ALL
SELECT 5, 'TA测试部', 2 FROM dual
)
SELECT RPAD(' ', 2 * (LEVEL - 1), '-') || DEPNAME "DEPNAME",
       CONNECT_BY_ROOT DEPNAME "ROOT",
       CONNECT_BY_ISLEAF "ISLEAF",
       LEVEL,
       SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"
  FROM DEP
 START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;
说明：
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

六. 取最底层叶子节点

WITH test AS(
SELECT '2' id_xhu,'1' id_lhu FROM dual UNION ALL
SELECT '3','2' FROM dual UNION ALL
SELECT '4','3' FROM dual UNION ALL
SELECT '5','3' FROM dual)
SELECT ID_XHU
  FROM TEST T
 WHERE NOT EXISTS (SELECT 1 FROM TEST B WHERE B.ID_LHU = T.ID_XHU)
 START WITH T.ID_LHU = '1'
CONNECT BY PRIOR ID_XHU = ID_LHU

 

七.列转行

with a as (
SELECT '11,2a2,33,2,34,,2,2' A FROM dual  )
SELECT  REGEXP_SUBSTR(A, '[^,]+', 1, LEVEL) A
          FROM A
        CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(A, '[^,]+'))+1