CONNECT BY理解

最新推荐文章于 2023-07-05 10:55:38 发布

我是阿普

最新推荐文章于 2023-07-05 10:55:38 发布

阅读量367

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/apu21/article/details/72956941

常见的，connect by会在构造序列的时候使用

select rownum from dual connect by rownum <= 12;

SELECT TO_CHAR(shijian, 'hh24:mi') shijian, ROWNUM r
FROM (SELECT TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || '0930',
'yyyymmdd hh24mi') + (ROWNUM - 1) / 12 SHIJIAN,
ROWNUM
FROM DUAL
CONNECT BY ROWNUM <= 12
ORDER BY shijian)
WHERE shijian BETWEEN
(CASE WHEN
TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'), 9, 3)) >= 73 AND
TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'), 9, 3)) <= 193 THEN
TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || '0930', 'yyyymmdd hh24mi') ELSE
TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || '2130', 'yyyymmdd hh24mi') END)
AND (CASE WHEN
TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'), 9, 3)) >= 73 AND
TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'), 9, 3)) <= 193 THEN
TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || '1930', 'yyyymmdd hh24mi') ELSE
TO_DATE(TO_CHAR(SYSDATE + 1, 'YYYYMMDD') || '0730',
'yyyymmdd hh24mi') END);