PUZZLEUP 2015

〇〇 · 发表于 2015-8-13 10:34

本帖最后由〇〇于 2015-8-13 10:42 编辑

lastwinner 发表于 2015-8-13 10:27
看89#，十来毫秒出

我看错了>800是调试语句

lastwinner · 发表于 2015-8-13 10:43

本帖最后由 lastwinner 于 2015-8-13 10:44 编辑

〇〇发表于 2015-8-13 10:34
>800有什么根据
说不定有个10位的呢

实际上最后是去掉800的限制，一开始只是为调试而做的限制
并且，代码里是注释掉大于800的限制了啊

〇〇 · 发表于 2015-8-13 10:59

lastwinner 发表于 2015-8-13 10:43
实际上最后是去掉800的限制，一开始只是为调试而做的限制
并且，代码里是注释掉大于800的限制了啊

每层这么少
select count(*)cnt,lvl from w group by lvl order by 2;
  2 3 4 5 6 7 8
   CNT       LVL
---------- ----------
   1108       2
   1870       3
   2448       4
   1378       5
   504       6
      47       7

已选择6行。

〇〇 · 发表于 2015-8-13 11:05

〇〇发表于 2015-8-13 10:59
每层这么少
select count(*)cnt,lvl from w group by lvl order by 2;
2 3 4 5 6 7 ...

我的前4层也很快,后面就不行了
SQL> with t as(select level-1 l from dual connect by level<=10)
,t3 as(select distinct a.l||b.l||c.l l from t a,t b,t c where a.l*a.l/2>b.l*c.l
and a.l<>b.l and b.l<>c.l and c.l<>a.l)
,t4 as(select distinct a.l||substr(b.l,-1) l from t3 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t5 as(select distinct a.l||substr(b.l,-1) l from t4 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t6 as(select distinct a.l||substr(b.l,-1) l from t5 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
select (select count(*) from t3)c3,
(select count(*) from t4)c4,
(select count(*) from t5)c5,
(select count(*) from t6)c6 from dual;  2 3 4 5 6 7 8 9 10 11 12 13

      C3       C4       C5       C6
---------- ---------- ---------- ----------
   342    1108    1870    2426

SQL> set timi on
SQL> /

      C3       C4       C5       C6
---------- ---------- ---------- ----------
   342    1108    1870    2426

已用时间:  00: 00: 00.02

〇〇 · 发表于 2015-8-13 11:08

〇〇发表于 2015-8-13 11:05
我的前4层也很快,后面就不行了
SQL> with t as(select level-1 l from dual connect by levelb.l*c.l
a ...

奇诡这么写10层都很快
SQL> with t as(select level-1 l from dual connect by level<=10)
,t3 as(select distinct a.l||b.l||c.l l from t a,t b,t c where a.l*a.l/2>b.l*c.l
and a.l<>b.l and b.l<>c.l and c.l<>a.l)
,t4 as(select distinct a.l||substr(b.l,-1) l from t3 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t5 as(select distinct a.l||substr(b.l,-1) l from t4 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t6 as(select distinct a.l||substr(b.l,-1) l from t5 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t7 as(select distinct a.l||substr(b.l,-1) l from t6 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t8 as(select distinct a.l||substr(b.l,-1) l from t7 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t9 as(select distinct a.l||substr(b.l,-1) l from t8 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t10 as(select distinct a.l||substr(b.l,-1) l from t9 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
select (select count(*) from t3)c3,
(select count(*) from t4)c4,
(select count(*) from t5)c5,
(select count(*) from t6)c6,
(select count(*) from t7)c7,
(select count(*) from t8)c8,
(select count(*) from t9)c9
(select count(*) from t10)c10
from dual;  2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
(select count(*) from t10)c10
*
第 25 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

已用时间:  00: 00: 00.00
SQL> 24
24* (select count(*) from t9)c9
SQL> a ,
24* (select count(*) from t9)c9,
SQL> /

      C3       C4       C5       C6       C7       C8       C9
---------- ---------- ---------- ---------- ---------- ---------- ----------
   C10
----------
   342    1108    1870    2426    1237       373       26
      0

已用时间:  00: 00: 00.06
SQL>

〇〇 · 发表于 2015-8-13 11:17

〇〇发表于 2015-8-13 11:08
奇诡这么写10层都很快
SQL> with t as(select level-1 l from dual connect by levelb.l*c.l
and a.lb ...

经过一番改写,我的也毫秒出了,缺点是人工认为9位最长
with t as(select level-1 l from dual connect by level<=10)
,t3 as(select distinct a.l||b.l||c.l l from t a,t b,t c where a.l*a.l/2>b.l*c.l
and a.l<>b.l and b.l<>c.l and c.l<>a.l)
,t4 as(select distinct a.l||substr(b.l,-1) l from t3 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t5 as(select distinct a.l||substr(b.l,-1) l from t4 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t6 as(select distinct a.l||substr(b.l,-1) l from t5 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t7 as(select distinct a.l||substr(b.l,-1) l from t6 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t8 as(select distinct a.l||substr(b.l,-1) l from t7 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t9 as(select distinct a.l||substr(b.l,-1) l from t8 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t10 as(select distinct a.l||substr(b.l,-1) l from t9 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,taiqiguaile as(select (select count(*) from t3)c3,
(select count(*) from t4)c4,
(select count(*) from t5)c5,
(select count(*) from t6)c6,
(select count(*) from t7)c7,
(select count(*) from t8)c8,
(select count(*) from t9)c9,
(select count(*) from t10)c10 ,t9.l
from dual,t9)
select max(l) from taiqiguaile;

solomon_007 · 发表于 2015-8-13 12:01

本帖最后由 solomon_007 于 2015-8-13 12:02 编辑

lastwinner 发表于 2015-8-13 09:32
先求出所有的符合条件的3位数
然后做connect by，尽可能的以9开头，尽可能的以较大的数衔接在右边（左边 ...

根据你的思路:

SQL> with t as (select rownum-1 n from dual connect by rownum<=10),
  2    s as (select a.n||b.n||c.n v
  3             from t a, t b, t c
  4             where a.n <> b.n
  5             and b.n <> c.n
  6             and a.n <> c.n
  7             and a.n*a.n > 2*b.n*c.n),
  8    r as (
  9 select level lvl,v,replace(sys_connect_by_path(decode(level,1,v,substr(v,length(v),1)),','),',','') str
10    from s
11 where level <=8
12  connect by nocycle prior substr(v,2) = substr(v,1,2))
13  select max(to_number(str))
14 from r
15 where instr(substr(str,1,length(str)-1),substr(v,length(v),1)) = 0
16  /
MAX(TO_NUMBER(STR))
-------------------
      985432107

〇〇 · 发表于 2015-8-13 12:09

〇〇发表于 2015-8-13 11:17
经过一番改写,我的也毫秒出了,缺点是人工认为9位最长
with t as(select level-1 l from dual connect by ...

改好了
with t as(select level-1 l from dual connect by level<=10)
,t3 as(select distinct a.l||b.l||c.l l from t a,t b,t c where a.l*a.l/2>b.l*c.l
and a.l<>b.l and b.l<>c.l and c.l<>a.l)
,t4 as(select distinct a.l||substr(b.l,-1) l from t3 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t5 as(select distinct a.l||substr(b.l,-1) l from t4 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t6 as(select distinct a.l||substr(b.l,-1) l from t5 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t7 as(select distinct a.l||substr(b.l,-1) l from t6 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t8 as(select distinct a.l||substr(b.l,-1) l from t7 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t9 as(select distinct a.l||substr(b.l,-1) l from t8 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,t10 as(select distinct a.l||substr(b.l,-1) l from t9 a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0)
,taihaole as(select nvl((select max(to_number(l)) from t3),0)c3,
nvl((select max(to_number(l)) from t4),0)c4,
nvl((select max(to_number(l)) from t5),0)c5,
nvl((select max(to_number(l)) from t6),0)c6,
nvl((select max(to_number(l)) from t7),0)c7,
nvl((select max(to_number(l)) from t8),0)c8,
nvl((select max(to_number(l)) from t9),0)c9,
nvl((select max(to_number(l)) from t10),0)c10
from dual)
select greatest(c3,c4,c5,c6,c7,c8,c9,c10) from taihaole;

〇〇 · 发表于 2015-8-13 12:11

solomon_007 发表于 2015-8-13 12:01
根据你的思路:

SQL> with t as (select rownum-1 n from dual connect by rownum 2*b.n*c.n),

和89楼很像

solomon_007 · 发表于 2015-8-13 12:24

〇〇发表于 2015-8-13 12:11
和89楼很像

一样的

[每日一题] PUZZLEUP 2015