在论坛上看到一篇帖子,尝试了写一下,发现基本上所有人都自连接了三次,笛卡尔积非常大,所以效率特别低,我的写法,只需要自连接两次,效率提升很多,不过我判断是否有重复数字的步骤,不是最好的,但是效率仍然没的说,毕竟笛卡尔积小了太多。
SQL> with t as
2 (select r
3 from (select level+122 r from dual connect by level<=(987-122))
4 where substr(r, 1, 1) <> substr(r, 2, 1)
5 and substr(r, 3, 1) <> substr(r, 2, 1)
6 and substr(r, 1, 1) <> substr(r, 3, 1)
7 and instr(r, 0) = 0
8 )
9 select count(*)
10 from t t1, t t2
11 where t1.r + t2.r < 1000
12 and t1.r < t2.r
13 and regexp_count(t1.r || t2.r || t1.r + t2.r, 1) = 1
14 and regexp_count(t1.r || t2.r || t1.r + t2.r, 2) = 1
15 and regexp_count(t1.r || t2.r || t1.r + t2.r, 3) = 1
16 and regexp_count(t1.r || t2.r || t1.r + t2.r, 4) = 1
17 and regexp_count(t1.r || t2.r || t1.r + t2.r, 5) = 1
18 and regexp_count(t1.r || t2.r || t1.r + t2.r, 6) = 1
19 and regexp_count(t1.r || t2.r || t1.r + t2.r, 7) = 1
20 and regexp_count(t1.r || t2.r || t1.r + t2.r, 8) = 1
21 and regexp_count(t1.r || t2.r || t1.r + t2.r, 9) = 1
22 ;
COUNT(*)
----------
168
Executed in 0.484 seconds
部分结果如下:
SQL> with t as
2 (select r
3 from (select level+122 r from dual connect by level<=(987-122))
4 where substr(r, 1, 1) <> substr(r, 2, 1)
5 and substr(r, 3, 1) <> substr(r, 2, 1)
6 and substr(r, 1, 1) <> substr(r, 3, 1)
7 and instr(r, 0) = 0
8 )
9 select t1.r+t2.r||'='||t1.r||'+'||t2.r
10 from t t1, t t2
11 where t1.r + t2.r < 1000
12 and t1.r < t2.r
13 and regexp_count(t1.r || t2.r || t1.r + t2.r, 1) = 1
14 and regexp_count(t1.r || t2.r || t1.r + t2.r, 2) = 1
15 and regexp_count(t1.r || t2.r || t1.r + t2.r, 3) = 1
16 and regexp_count(t1.r || t2.r || t1.r + t2.r, 4) = 1
17 and regexp_count(t1.r || t2.r || t1.r + t2.r, 5) = 1
18 and regexp_count(t1.r || t2.r || t1.r + t2.r, 6) = 1
19 and regexp_count(t1.r || t2.r || t1.r + t2.r, 7) = 1
20 and regexp_count(t1.r || t2.r || t1.r + t2.r, 8) = 1
21 and regexp_count(t1.r || t2.r || t1.r + t2.r, 9) = 1
22 and rownum<10
23 ;
T1.R+T2.R||'='||T1.R||'+'||T2.
--------------------------------------------------------------------------------
783=124+659
864=125+739
486=127+359
495=127+368
495=128+367
567=128+439
486=129+357
567=129+438
783=129+654
9 rows selected
Executed in 0.156 seconds
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30123160/viewspace-2051918/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30123160/viewspace-2051918/