Oracle 伪列Level的应用
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 伪列Level的应用,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含1660字,纯文字阅读大概需要3分钟。
内容图文
![Oracle 伪列Level的应用](/upload/InfoBanner/zyjiaocheng/878/9a26c1c3dc134cd3948af9668d4b340d.jpg)
目录导航:
1. 根据ID的数量按序展开
2. 根据ID的数量按序展开
3. 生成随机数
4. 转置截取字符串
1、根据id的数量按序展开
WITH TB AS
(SELECT 'U101' USER_ID, DATE '2016-11-02' START_DT, DATE '2016-11-06' END_DT
FROM DUAL
UNION ALL
SELECT 'U102', DATE '2015-06-01', DATE '2015-06-03' FROM DUAL)
SELECT TB.USER_ID, TB.START_DT + LEVEL - 1
FROM TB
CONNECT BY TB.USER_ID = PRIOR TB.USER_ID
AND LEVEL <= (TB.END_DT - TB.START_DT + 1)
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;
2. 根据id的数量按序展开
WITH TB AS
(SELECT 'U101' USER_ID, '12345' MOBILE, 3 CNT
FROM DUAL
UNION ALL
SELECT 'U102', '33563', 4 FROM DUAL)
SELECT A.USER_ID, A.MOBILE, B.LV
FROM TB A
LEFT JOIN (SELECT TB.USER_ID, LEVEL LV
FROM TB
CONNECT BY TB.USER_ID = PRIOR TB.USER_ID
AND LEVEL <= CNT
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) B
ON A.USER_ID = B.USER_ID
3. 生成随机数
SELECT DBMS_RANDOM.VALUE
FROM DUAL
CONNECT BY LEVEL <= 100;
SELECT SYSDATE - LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100;
SELECT TO_DATE('2020/01/02 08:00:00', 'yyyy-MM-dd HH24:mi:ss') +
(LEVEL - 1) DT
FROM DUAL
CONNECT BY LEVEL <= 7;
4. 转置截取字符串
SELECT SUBSTR('大家好', LEVEL, 1)
FROM DUAL
CONNECT BY LEVEL <= LENGTH('大家好');
WITH TB AS
(SELECT 'U1' USER_ID, '哈喽,世界' STR
FROM DUAL
UNION ALL
SELECT 'U2', '你好啊,地球' FROM DUAL)
SELECT TB.USER_ID, SUBSTR(STR, LEVEL, 1)
FROM TB
CONNECT BY TB.USER_ID = PRIOR TB.USER_ID
AND LEVEL <= LENGTH(STR)
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;
WITH tb AS
(SELECT 'a' rn, '1,2,3' i_name
FROM dual
UNION ALL
SELECT 'b', '4,5,6' FROM dual)
SELECT rn, regexp_substr(i_name, '[^,]+', 1, LEVEL)
FROM tb
CONNECT BY PRIOR dbms_random.value IS NOT NULL
AND PRIOR rn = rn
AND LEVEL <= length(i_name) - length(REPLACE(i_name, ',', '')) + 1;
内容总结
以上是互联网集市为您收集整理的Oracle 伪列Level的应用全部内容,希望文章能够帮你解决Oracle 伪列Level的应用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。