--1、start with + connect by -->一般用于构成树查询
--eg:
SELECT *
FROM district
WHERE del_flag = 0 AND (district_level_code IN (SELECT DISTINCT domain_key FROM domain_district_level) OR district_level_code IS NULL)
START WITH district_id IN (SELECT district_id --根节点开始的位置
FROM district WHERE parent_district_id IS NULL
OR parent_district_id = 0)
CONNECT BY PRIOR district_id = parent_district_id
ORDER BY LEVEL, district_no;
--2、connect by 用于构成循环
select LEVEL FROM dual CONNECT BY LEVEL<=10;
SELECT ROWNUM FROM dual CONNECT BY ROWNUM <=10;
SELECT LEVEL FROM dual CONNECT BY 1=1; --会得到无穷数列.
SELECT ROWNUM FROM dual CONNECT BY 1=1; --会得到无穷数列.
rownum与 level 一致,都是以connect by 后的数字范围为准。
select level from dual connect by level< 10;
select rownum from dual connect by rownum< 10; --都能取到9
select level from dual connect by level<= 10;
select rownum from dual connect by rownum< =10; --都能取到10