一道高级SQL题

碧水幽幽泉 2014-06-18 08:35:16
表结构和数据如下:
ID NAME
1 E1~E4
2 F3~F9
。。。。。

要求实现如下效果:
1 E1
1 E2
1 E3
1 E4
2 F3
2 F4
2 F5
2 F6
2 F7
2 F8
2 F9
...全文
294 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
碧水幽幽泉 2014-06-20
  • 打赏
  • 举报
回复
引用 13 楼 zlloct 的回复:
[quote=引用 12 楼 qq646748739 的回复:] 再请教下,prior dbms_random.value is not null; 是如何去重的? 为什么使用 is not null,而不是is null? 谢谢!
dbms_random.value会随机产生值,prior dbms_random.value is null只能查出没一次循环的第一个值。 prior dbms_random.value is not null; 的作用是防止循环的时候同一行数据再次出现引起报错[/quote] 嗯,理解了,谢谢! 结贴了。
CT_LXL 2014-06-19
  • 打赏
  • 举报
回复
引用 7 楼 z_shousi 的回复:
[quote=引用 5 楼 z_shousi 的回复:] [quote=引用 3 楼 zlloct 的回复:] [quote=引用 2 楼 qq646748739 的回复:] [quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 [/quote] 但是 这个正则截取只能截取一位数字。E2-E12。[/quote]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[0-9]+', 1, 1) fr_num,
               regexp_substr(t1.name, '[0-9]+', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
[/quote] 谢谢指正,我修改了一下,见#9
CT_LXL 2014-06-19
  • 打赏
  • 举报
回复
引用 8 楼 diyyong 的回复:
[quote=引用 6 楼 diyyong 的回复:] [quote=引用 3 楼 zlloct 的回复:] [quote=引用 2 楼 qq646748739 的回复:] [quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 [/quote] 先膜拜一下版主,思路很不错,学习了 指出一个小问题,regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 这里应该是取最后一个数字,而不是第二个吧? substr(name, -1)[/quote] 我理解错了,我以为是 ID NAME 1 E1E2E3E4 [/quote] 谢谢指正,我修改了一下,你可以再看看有没有问题
CT_LXL 2014-06-19
  • 打赏
  • 举报
回复
引用 2 楼 qq646748739 的回复:
[quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] 修改了一下:
select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]+', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]+', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]+', 1,2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
diyyong 2014-06-19
  • 打赏
  • 举报
回复
引用 6 楼 diyyong 的回复:
[quote=引用 3 楼 zlloct 的回复:] [quote=引用 2 楼 qq646748739 的回复:] [quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 [/quote] 先膜拜一下版主,思路很不错,学习了 指出一个小问题,regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 这里应该是取最后一个数字,而不是第二个吧? substr(name, -1)[/quote] 我理解错了,我以为是 ID NAME 1 E1E2E3E4
  • 打赏
  • 举报
回复
引用 5 楼 z_shousi 的回复:
[quote=引用 3 楼 zlloct 的回复:] [quote=引用 2 楼 qq646748739 的回复:] [quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 [/quote] 但是 这个正则截取只能截取一位数字。E2-E12。[/quote]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[0-9]+', 1, 1) fr_num,
               regexp_substr(t1.name, '[0-9]+', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
diyyong 2014-06-19
  • 打赏
  • 举报
回复
引用 3 楼 zlloct 的回复:
[quote=引用 2 楼 qq646748739 的回复:] [quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 [/quote] 先膜拜一下版主,思路很不错,学习了 指出一个小问题,regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 这里应该是取最后一个数字,而不是第二个吧? substr(name, -1)
  • 打赏
  • 举报
回复
引用 3 楼 zlloct 的回复:
[quote=引用 2 楼 qq646748739 的回复:] [quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 [/quote] 但是 这个正则截取只能截取一位数字。E2-E12。
  • 打赏
  • 举报
回复
引用 3 楼 zlloct 的回复:
[quote=引用 2 楼 qq646748739 的回复:] [quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 [/quote] prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。 这个是好办法啊。
CT_LXL 2014-06-19
  • 打赏
  • 举报
回复
引用 12 楼 qq646748739 的回复:
再请教下,prior dbms_random.value is not null; 是如何去重的? 为什么使用 is not null,而不是is null? 谢谢!
dbms_random.value会随机产生值,prior dbms_random.value is null只能查出没一次循环的第一个值。 prior dbms_random.value is not null; 的作用是防止循环的时候同一行数据再次出现引起报错
碧水幽幽泉 2014-06-19
  • 打赏
  • 举报
回复
再请教下,prior dbms_random.value is not null; 是如何去重的? 为什么使用 is not null,而不是is null? 谢谢!
CT_LXL 2014-06-19
  • 打赏
  • 举报
回复
引用 2 楼 qq646748739 的回复:
[quote=引用 1 楼 zlloct 的回复:]

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢![/quote] regexp_substr(t1.name, '[[:alpha:]]', 1, 1) 取字母 regexp_substr(t1.name, '[[:digit:]]', 1, 1) 取首个数字 regexp_substr(t1.name, '[[:digit:]]', 1, 2) 取第二个数字 level <= to_num - fr_num + 1 循环次数,如fr_num=1,to_num=4,那么循环4-1+1=4次 id = prior id 在每组ID中循环,如ID=1循环完了后,id=2内又从level=1开始循环 prior dbms_random.value is not null ORACLE有个检查,如果有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。
碧水幽幽泉 2014-06-18
  • 打赏
  • 举报
回复
引用 1 楼 zlloct 的回复:

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;
版主,你这个高级SQ我L没太看懂,求解释下,谢谢!
CT_LXL 2014-06-18
  • 打赏
  • 举报
回复
引用 楼主 qq646748739 的回复:
表结构和数据如下: ID NAME 1 E1~E4 2 F3~F9 。。。。。 要求实现如下效果: 1 E1 1 E2 1 E3 1 E4 2 F3 2 F4 2 F5 2 F6 2 F7 2 F8 2 F9

select id, nm || (level + fr_num - 1)
  from (SELECT t1.id id,
               regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
               regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
               regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
          FROM T1)
connect by level <= to_num - fr_num + 1
       and id = prior id
       and prior dbms_random.value is not null;

3,492

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧