PUZZLEUP 2015

〇〇

〇〇 发表于 2015-8-13 12:09
改好了
with t as(select level-1 l from dual connect by levelb.l*c.l
and a.lb.l and b.lc.l and c. ...

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 max(to_number(nvl(l,0)))c from t3 union all
select max(to_number(nvl(l,0))) from t4 union all
select max(to_number(nvl(l,0))) from t5 union all
select max(to_number(nvl(l,0))) from t6 union all
select max(to_number(nvl(l,0))) from t7 union all
select max(to_number(nvl(l,0))) from t8 union all
select max(to_number(nvl(l,0))) from t9 union all
select max(to_number(nvl(l,0))) from t10
)
select max(c) from taihaole;
也可以

〇〇

〇〇 发表于 2015-8-13 12:40
with t as(select level-1 l from dual connect by levelb.l*c.l
and a.lb.l and b.lc.l and c.la.l)
, ...

递归很快,为什么88楼人工展开很慢
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)
,tn(lv,l) as(
select 3 lv, l from t3 union all
select lv+1, a.l||substr(b.l,-1) l from tn a,t3 b
where substr(a.l,-2,2)=substr(b.l,1,2) and instr(a.l,substr(b.l,-1))=0 and lv<=9)
select max(to_number(nvl(l,0)))c from tn;

newkid

为什么整那么复杂呢？

WITH t(str) AS (
SELECT CAST(LEVEL AS VARCHAR2(10)) FROM DUAL WHERE LEVEL>=10 AND MOD(LEVEL,11)<>0 CONNECT BY LEVEL<=99
UNION ALL
SELECT t.str||n
  FROM t,(SELECT LEVEL-1 n FROM DUAL CONNECT BY LEVEL<=10)
WHERE INSTR(t.str,n)=0 AND SUBSTR(t.str,-2,1)*SUBSTR(t.str,-2,1)/2>SUBSTR(t.str,-1,1)*n
)
SELECT MAX(TO_NUMBER(str)) FROM t;

MAX(TO_NUMBER(STR))
-------------------
          985432107

Elapsed: 00:00:00.10

newkid

lugionline 发表于 2015-8-13 08:19
985432107 可以手工算？

把证明最大值的思路写出来给你发章。

lugionline

newkid 发表于 2015-8-13 22:50
把证明最大值的思路写出来给你发章。

证明来了

In[1]:= Max@FixedPointList[
   Select[Flatten@Table[#*10 + i, {i, 0, 9}],
     ! MemberQ[#[[1 ;; -2]], #[[-1]]]
         && (Length[#] < 3 || #[[-3]]^2/2 > #[[-2]]*#[[-1]]) &
        @IntegerDigits[#] &] &,
   Range[9]][[-3]]

Out[1]= 985432107

呵呵，没有证明，我只是从9开始猜了一个，后面的数字都是顺理成章的，章好像没什么用，给其它小伙伴吧

M 就是比SQL短，Spark到哪里去了

〇〇

newkid 发表于 2015-8-13 22:49
为什么整那么复杂呢？

WITH t(str) AS (

88楼为什么慢

newkid

〇〇 发表于 2015-8-14 05:48
88楼为什么慢

GREATEST(C3,C4,C5,C6,C7,C8,C9,C10)
----------------------------------
                         985432107

Elapsed: 00:00:00.08


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 coalesce(
   (select max(to_number(l)) from t10)
  ,(select max(to_number(l)) from t9)
  ,(select max(to_number(l)) from t8)
  ,(select max(to_number(l)) from t7)
  ,(select max(to_number(l)) from t6)
  ,(select max(to_number(l)) from t5)
  ,(select max(to_number(l)) from t4)
  ,(select max(to_number(l)) from t3)
  ) as num
from dual;

       NUM
----------
985432107

Elapsed: 00:00:00.03

〇〇

newkid 发表于 2015-8-14 08:20
GREATEST(C3,C4,C5,C6,C7,C8,C9,C10)
----------------------------------
                         9 ...

我的意思是：只写一个t10就很慢，多写几个反而快

newkid

全加上ROWNUM就老实了：

with t as(select level-1 l from dual connect by level<=10)
,t3 as(select distinct a.l||b.l||c.l l,ROWNUM 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,ROWNUM 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,ROWNUM 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,ROWNUM 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,ROWNUM 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,ROWNUM 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,ROWNUM 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,ROWNUM 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 count(l) from t10;

newkid

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),

你这WHERE LEVEL<=8是什么意思？如果早知道不超过8位也要加在CONNECT BY条件。