Oracle趣味题1

题目:

1～9有9个数字，三个三个一组，可能正好能组成一个加法等式
比如：124＋659＝783
给出所有的结果，使用工具不限制

 with tmp as(
  2  select a.num from (select rownum as num from dual connect by rownum <= 999) a
  3  where substr(a.num,1,1) != substr(a.num,2,1)
  4  and substr(a.num,3,1) != substr(a.num,2,1)
  5  and substr(a.num,1,1) != substr(a.num,3,1) and instr(a.num,0)=0
  6  and a.num >= 123 and a.num<=987)
  7  --
  8  select count(*) from(
  9  select /*+rule */
10  a.num || '+' || b.num || '=' || c.num as str
11  from tmp a, tmp b, tmp c
12  where replace(replace(replace(
13        replace(replace(replace(
14        replace(replace(replace('123456789',substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)),
15                                            substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)),
16                                            substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) is null
17        and a.num + b.num = c.num
18        and a.num < b.num
19        and b.num < c.num
20  );

with t as(select x from (select level+122 x from dual connect by level<=(987-122)) where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1
)<>substr(x,3,1)
  2  and  substr(x,2,1)<>substr(x,3,1) and instr(x,0)=0),
  3  a as (select rownum rn,x from t where x<494)
  4  select count(*) from (
  5  select /*+rule*/ a.x||'+'||b.x||'-'||c.x||'==0' from a a,t b,t c
  6  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x --and b.x<c.x
  7  and not exists(select 1 from (select rownum l from dual connect by level<=9)where instr(a.x||b.x||c.x ,l,1,2)>0))
  8  --and rownum<=10;

  
 where replace(replace(replace(
        replace(replace(replace(
         replace(replace(replace('123456789',substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)),
                                            substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)),
                                           substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) is null

修改为:
translate('123456789','0' || a.num || b.num || c.num,'0') is null
会快不少.

总结：with as:为了简化SQL语句，可以将语句分成若干个视图来操作，但是创建的试图将会作为对象保存在数据库中，但经常有一些语句只是临时使用，所以在sql-99规范中有了with as 语句，该语句实质上就是创建临时视图，来帮助你简化语句并使语句结构更清晰更容易阅读。
   translate:translate：字符级别的代替
   from_string中的#被to_string中的#代替，但char中又没有#字符，所以通过这一步躲开了to_string必须不为空的规则。然后后面的数字以及小数点都转换为空，于是原来的字符串中只留下abc三个字符。
   replace:字符串级别的代替,一般和substr一起使用
   /*+rule+/ 基于规则的优化
   connect by:
   -- 2、connect by 用于构成循环
   select LEVEL FROM dual CONNECT BY LEVEL<=10;
   SELECT ROWNUM FROM dual CONNECT BY ROWNUM <=10;
   SELECT LEVEL FROM dual CONNECT BY 1=1; --会得到无穷数列.
   SELECT ROWNUM FROM dual CONNECT BY 1=1; --会得到无穷数列.
   
   -- 标识符 LEVEL、ROWID 和 ROWNUM 是 Oracle 中的伪列，可用于查询设计器和视图设计器所创建的查询。可将伪列输入到“网格”窗格或直接输入到“SQL”窗格。它们不出现在“关系图”窗格中的输入源窗口。