开发面试题

〇〇 · 发表于 2016-12-24 08:01

newkid 发表于 2016-12-23 22:41
with d as (
select '1121121123115' str from dual
)

很好很强大
SQL> with d as (
  2  select '112112112113224225123' str from dual
  3  )
  4  ,n as (
  5  select level n,str from d connect by level<=length(str)
  6  )
  7  select s1||'+'||s2||'='||to_char(s1+s2)
  8 from (select substr(n1.str,n1.n,n2.n) s1
  9             ,substr(n1.str,n1.n+n2.n,n3.n) s2
10             ,substr(n1.str,n1.n+n2.n+n3.n) s3
11          from n n1,n n2,n n3
12       )
13  where s3 like to_char(s1+s2)||'%';

S1||'+'||S2||'='||TO_CHAR(S1+S2)
--------------------------------------------------------------
1+1=2
112112+112113=224225
12112+1=12113
1+1=2
1+1=2
12+1=13
2+2=4
1+2=3

solomon_007 · 发表于 2016-12-24 13:00

newkid 发表于 2016-12-23 22:41
with d as (
select '1121121123115' str from dual
)

大师一出手，其他立马相形见绌

kinghow · 发表于 2016-12-26 10:48

本帖最后由 kinghow 于 2016-12-26 10:51 编辑

好简洁，好强大。我当时写了个复杂的。

CREATE TABLE T1(

  R  VARCHAR2(10 BYTE),
  A  NUMBER,
  B  NUMBER,
  C  NUMBER

);

CREATE OR REPLACE PROCEDURE prc_test1 (X NUMBER)

AS
v_str VARCHAR2(1000);
v_len NUMBER;
v_b2 NUMBER;
v_a    VARCHAR2(1000);
v_b    VARCHAR2(1000);
v_c    VARCHAR2(1000);
BEGIN
v_str :=TO_CHAR(x);
v_len :=LENGTH(v_str);
DELETE FROM t1;
FOR n IN 1..v_len
LOOP
   FOR a IN 1 .. ROUND((v_len-n - 1)/2)
   LOOP
      v_a :=SUBSTR(v_str,n, a);
      v_b2:=LEAST(ROUND((v_len-n - a)/2), (v_len-n - 2* a+1));
      FOR b IN 1..v_b2
      LOOP
         v_b := SUBSTR(v_str,n + a,b);
         v_c := SUBSTR(v_str,n + a+b, LENGTH(v_a+v_b));
         INSERT INTO t1
            VALUES (CASE WHEN v_a + v_b =v_c THEN '√' END,
                     v_a,
                     v_b,
                     v_c);
      END LOOP;
   END LOOP;
END LOOP;
COMMIT;
END;
/

exec prc_test1 (112358);
select * from t1;
√  1    1    2
   1    12 35
   11 2    35
   11 23 58
√  1    2    3
   1    23 58
   12 3    58
√  2    3    5
√  3    5    8
这里把所有可能的截取都列出了，方便调试。