ORACLE LEVEL,CONNECT BY,PRIOR,START WITH

最新推荐文章于 2024-06-19 22:06:22 发布

原创最新推荐文章于 2024-06-19 22:06:22 发布 · 662 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

20 篇文章

订阅专栏

本文介绍如何利用 Oracle SQL 中的 LEVEL 关键字来处理字符串，将其按分隔符分割为多列。通过示例展示了如何使用 LEVEL 和正则表达式函数处理规则和不规则的字符串，并提供了生成日期序列的技巧。

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'))

在这里插入图片描述

资料
 转载.