SELECT LEVEL, MNAME, MENU_ID, PARENT_ID, PRIOR MNAME AS PNAME
FROM SYS_MENU
CONNECT BY PARENT_ID = PRIOR MENU_ID
START WITH PARENT_ID IS NULL
扩展 level的妙用
1、当需要把一个字符串按某一分隔符分隔后,变为数据列,即把字符串行变为列,可以使用level关键字,例子:
with t as
(select 'a;b;c;d;e' as str from dual)
select level,
t.str,
substr(t.str, 2 * (level - 1) + 1, 1) as str_signle
from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;
运行结果:
2、上面的写法只是适用于一般有规律的字符串行,当遇到不规则字符串行时,可以使用oracle的正则表达式函数,请看下面的例子:
with t as
(select 'i;am;a;test;hahahhah' as str from dual)
select level,
str,
regexp_substr(t.str, '[^;]+', 1, level) str_single
from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;
运行结果:
或者不使用正则表达式:
with t_org as
(select 'I am a complicated string' as str from dual),
t_sep as
(select ' ' as sep from dual),
t as
(select t_org.str as orign_str,
t_sep.sep || t_org.str || t_sep.sep as str
from t_org,
t_sep)
select level,
t.orign_str,
/* instr(t.str, t_sep.sep, 1, level) as separator_postion,
instr(t.str, t_sep.sep, 1, level) + 1 as str_postion_begin,
instr(t.str, t_sep.sep, 1, level + 1) -
instr(t.str, t_sep.sep, 1, level) - 1 as str_single_len,*/
substr(t.str, instr(t.str, t_sep.sep, 1, level) + 1, instr(t.str, t_sep.sep, 1, level + 1) -
instr(t.str, t_sep.sep, 1, level) - 1) as str_signle
from t,
t_sep
connect by level < length(t.str) - length(replace(t.str, t_sep.sep, ''));
小技巧
- 生成当前整个月信息
SELECT TRUNC(SYSDATE,'MM')+ROWNUM-1 AS TEMPDAYTIME
FROM DUAL
CONNECT BY ROWNUM<=TO_CHAR(LAST_DAY(SYSDATE),'dd')
--生成 日
SELECT TRUNC(SYSDATE,'MM')+ROWNUM-1 AS TEMPDAYTIME
FROM DUAL
CONNECT BY ROWNUM<=TO_CHAR(LAST_DAY(SYSDATE),'dd')
--生成 月
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), ROWNUM - 1) AS MONTHTIME
FROM DUAL
CONNECT BY LEVEL < = 12
- 按时间范围生成日期
select to_char(trunc( to_date('2021-06-01'))-level,'YYYY-MM-DD') as date_dt
from dual
connect by level <= add_months( to_date('2021-06-01'),17)-trunc( to_date('2021-06-01'))