connect by sql

用法实例：

Basically if table has one row, we can get what we expected.
If more than one rows, totally mass and we need to use distinct to get what we want.
But if the table has many rows, it will be very slow.
Below is the test sql for your references:
create table test(id int, a int, b int);
insert into test values(1,5,8);

-- we are expecting 4 rows of 5,6,7,8, below sql can do
select id,a,b, a+level-1 from test connect by level<=b-a+1;

-- add one more row to the table
insert into test values(2,10,13);

-- we are expecting 4 rows of 5,6,7,8 and 4 rows of 10,11,12,13, so totally 8
-- below sql works but without distinct got 30 rows
select distinct id,a,b, a+level-1 c from test connect by level<=b-a+1 order by a, c;

-- if we add many rows to table, above sql will run forever, i think due to too much connect by and distinct
insert into test select rownum+2,rownum, rownum+3 from dual connect by level<=1000;
select distinct id,a,b, a+level-1 c from test connect by level<=b-a+1 order by a, c; -- run forever

-- here comes a interesting sql i noticed from internet, it works well and also very quick
-- any idea about it, i can not understand
-- i guess the key point is what does CONNECT BY exactly do in ORACLE
select id,a,b,a+level-1 c from test connect by level<=b-a+1 and prior id=id and prior dbms_random.value is not null order by a,c;

表结构：
pm_ci ci_id stu_ids
       1     1,2,3,4
       2     1,4
查询结果为：
        1   1
        1   2
        1   3
        1   4
         2   1
         2    4
其中需要注意正则表达式的用法
SELECT ci_id, regexp_substr(stu_ids, '[^,]+', 1, level) stu_id, level
  FROM pm_ci
CONNECT BY level <= length(regexp_replace(stu_ids, '[^,]+')) + 1
  and PRIOR ci_id = ci_id
         AND PRIOR dbms_random.value is not null
order by ci_id asc, stu_id asc

oracle的行列转换函数：wm_concat
select t.pay_orderno,
       wm_concat(t.agent_orderno) ordernolist,
       count(*) countnum
  from eir_pay t
where t.pay_orderno is not null
group by pay_orderno
having count(*) > 2
查询一个订单号对应的多个业务号

另外oracle的分析函数 lag和lead 也可实现某些特殊的需求