SQL分享（ AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL）是否多余

luoyanqun 2016-11-03 04:22:14

加精

SQL分享（ AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL）是否多余，剔除是否有影响呢？

SELECT 'TEST2' TABLE_NAME,
REGEXP_SUBSTR('COL1,COL2','[^,]+',1,LEVEL) COLUMN_NAME FROM DUAL
CONNECT BY LEVEL<=LENGTH(REGEXP_REPLACE('COL1,COL2','[^,]+'))+1 AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL；

SELECT 'TEST2' TABLE_NAME,
REGEXP_SUBSTR('COL1,COL2','[^,]+',1,LEVEL) COLUMN_NAME FROM DUAL
CONNECT BY LEVEL<=LENGTH(REGEXP_REPLACE('COL1,COL2','[^,]+'))+1

...全文

7891 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

ChinaITOldMan 2017-12-30

打赏
举报

学习了，thanks!

Passage2011 2017-01-16

打赏
举报

引用 13 楼 wmxcn2000 的回复:

[quote=引用 12 楼 q12344566789 的回复:] 加了一个distinct，是不是感觉有点硬凑啊。另外标红处去掉，结果也是一样的。。


-- 加一个 PK 列
with TEST2 as
 (select 'COL1,COL2' as col_name, 1 id 
    from dual
  union all
  select 'col3,col4', 2
    from dual
)

SELECT 'TEST2' TABLE_NAME,
       REGEXP_SUBSTR(col_name, '[^,]+', 1, LEVEL) COLUMN_NAME
  FROM TEST2
CONNECT BY PRIOR id = id 
and LEVEL <=  regexp_count(col_name,',') + 1 
and PRIOR DBMS_RANDOM.VALUE IS NOT NULL

[/quote] 多列分割呢

jiangliangtong 2016-11-28

打赏
举报

学习了

细水长流- 2016-11-22

打赏
举报

otpub 2016-11-21

打赏
举报

学习了，大神厉害！

大饼Ora 2016-11-10

打赏
举报


with TEST2 as
 (select 'COL1,COL2' as col_name
    from dual
  union all
  select 'col3,col4'
    from dual)

SELECT 'TEST2' TABLE_NAME,
       REGEXP_SUBSTR(TEST2.col_name, '[^,]+', 1, LEVEL) COLUMN_NAME
  FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(TEST2.col_name, '[^,]+')) + 1
       AND PRIOR sysdate IS NOT NULL;

报错Test2.col_name 无效定义。。。是哪里弄错了吗

卖水果的net 2016-11-10

打赏
举报

引用 12 楼 q12344566789 的回复:

加了一个distinct，是不是感觉有点硬凑啊。另外标红处去掉，结果也是一样的。。


-- 加一个 PK 列
with TEST2 as
 (select 'COL1,COL2' as col_name, 1 id 
    from dual
  union all
  select 'col3,col4', 2
    from dual
)

SELECT 'TEST2' TABLE_NAME,
       REGEXP_SUBSTR(col_name, '[^,]+', 1, LEVEL) COLUMN_NAME
  FROM TEST2
CONNECT BY PRIOR id = id 
and LEVEL <=  regexp_count(col_name,',') + 1 
and PRIOR DBMS_RANDOM.VALUE IS NOT NULL

大饼Ora 2016-11-10

打赏
举报

引用 11 楼 wmxcn2000 的回复:

大神，还是有点问题。这样的结果是


with TEST2 as
 (select 'COL1,COL2' as col_name
    from dual
  union all
  select 'col3,col4'
    from dual)

SELECT distinct 'TEST2' TABLE_NAME,
       REGEXP_SUBSTR(col_name, '[^,]+', 1, LEVEL) COLUMN_NAME
  FROM TEST2
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(col_name, '[^,]+')) + 1
       and PRIOR DBMS_RANDOM.VALUE IS NOT NULL;


   	TABLE_NAME	COLUMN_NAME
1	TEST2	                         COL2
2	TEST2	                          COL1
3	TEST2	                          col4
4	TEST2	                          col3

加了一个distinct，是不是感觉有点硬凑啊。另外标红处去掉，结果也是一样的。。

卖水果的net 2016-11-10

打赏
举报

引用 10 楼 q12344566789 的回复:

我又闹乌龙了。。。from test2 不就好了吗。。傻掉了我

大饼Ora 2016-11-10

打赏
举报

引用 9 楼 q12344566789 的回复:

[quote=引用 8 楼 q12344566789 的回复:]


with TEST2 as
 (select 'COL1,COL2' as col_name
    from dual
  union all
  select 'col3,col4'
    from dual)

SELECT 'TEST2' TABLE_NAME,
       REGEXP_SUBSTR(TEST2.col_name, '[^,]+', 1, LEVEL) COLUMN_NAME
  FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(TEST2.col_name, '[^,]+')) + 1
       AND PRIOR sysdate IS NOT NULL;

报错Test2.col_name 无效定义。。。是哪里弄错了吗

@卖水果的net 帮忙看看[/quote] 我又闹乌龙了。。。from test2 不就好了吗。。傻掉了我

大饼Ora 2016-11-10

打赏
举报

引用 8 楼 q12344566789 的回复:


with TEST2 as
 (select 'COL1,COL2' as col_name
    from dual
  union all
  select 'col3,col4'
    from dual)

SELECT 'TEST2' TABLE_NAME,
       REGEXP_SUBSTR(TEST2.col_name, '[^,]+', 1, LEVEL) COLUMN_NAME
  FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(TEST2.col_name, '[^,]+')) + 1
       AND PRIOR sysdate IS NOT NULL;

报错Test2.col_name 无效定义。。。是哪里弄错了吗

@卖水果的net 帮忙看看