首页 » Oracle » Oracle伪列Level的使用

Oracle伪列Level的使用

2016-12-07 10:10:10阅读(283)

Oracle伪列Level的使用 1 根据ID的数量按序展开

现有表数据如下:

用户ID 开始日期 结束日期 USER_ID START_DT END_DT U101 2016-11-02 2016-11-06 U102 2015-06-01 2015-06-03

要生成下面的数据:

用户ID 日期 U101 2016-11-02 U101 2016-11-03 U101 2016-11-04 U101 2016-11-05 U101 2016-11-06 U102 2015-06-01 U102 2015-06-02 U102 2015-06-03
    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的数量按序展开

现有表数据如下:

用户ID 手机号 数量 USER_ID Mobile CNT U101 12345 3 U102 33563 4

要生成下面的数据:

用户ID 手机号 序号 USER_ID MOBILE LV U101 12345 1 U101 12345 2 U101 12345 3 U102 33563 1 U102 33563 2 U102 33563 3 U102 33563 4
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;
4 转置截取字符串 STR 大 家 好
    SELECT SUBSTR('大家好',LEVEL,1)
      FROM DUAL
    CONNECT BY LEVEL<=LENGTH('大家好');
USER_ID STR U1 哈 U1 喽 U1 , U1 世 U1 界 U2 你 U2 好 U2 啊 U2 , U2 地 U2 球
    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;
RN STR a 1 a 2 a 3 b 4 b 5 b 6
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;

最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹