Puzzleup 2010 比赛快开始了，大家用SQL解答啊

〇〇 · 发表于 2010-9-2 09:51

原帖由 lastwinner 于 2010-9-2 08:55 发表
bs，居然不贴sql

就是newkid的代码。。。
增加
WHERE substr(SUBSTR(str,p.pos,p.l),-1)<>'0'

PATH||STR
------------------------------------------------------------------------
----------------
38(7),38(49),(3)82401,98(24)01,98(5)7601,98(2)57601,98457(6)01,984573601

已用时间: 00: 01: 58.75

〇〇 · 发表于 2010-9-2 11:30

我一直以为递归第一层没有重复数字字母，其实不是的

with t as
(SELECT REPLACE(SYS_CONNECT_BY_PATH(n,','),',')a ,LEVEL l,''c
FROM (SELECT ROWNUM-1 n FROM DUAL CONNECT BY ROWNUM<=10)
START WITH n>0
CONNECT BY NOCYCLE LEVEL<=3)
select * from(select a from t minus select to_char(level,'fm999') a from dual connect by level<=999)where rownum<=10;

〇〇 · 发表于 2010-9-2 12:24

如果第一步就不允许重复数字字母

WITH p AS (
SELECT pos,l
FROM (SELECT ROWNUM pos FROM DUAL CONNECT BY ROWNUM<=10),(SELECT ROWNUM l FROM DUAL CONNECT BY ROWNUM<=3)
  )
,t (str,len,path) AS (
/*SELECT REPLACE(SYS_CONNECT_BY_PATH(n,','),',') ,LEVEL,''
  FROM (SELECT ROWNUM-1 n FROM DUAL CONNECT BY ROWNUM<=10)
START WITH n>0
CONNECT BY NOCYCLE LEVEL<=3
*/
select a,length(a),'' from (select to_char(level,'fm999') a from dual connect by level<=999)
where a<=9
or(a>=10 and a<=99 and mod(a,11)>0)
or(a>=100 and (substr(a,1,1)<>substr(a,2,1) and substr(a,1,1)<>substr(a,3,1) and substr(a,3,1)<>substr(a,2,1)))
UNION ALL
SELECT SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l)
   ,LENGTH(SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l))
   ,path||SUBSTR(str,1,pos-1)||'('||SUBSTR(str,pos,l)||')'||SUBSTR(str,pos+l)||','
FROM t,p
  WHERE pos+l-1<=t.len  and substr(SUBSTR(str,pos,l),-1)<>'0' and SUBSTR(str,p.pos,p.l)>'1'
      AND LENGTH(TRANSLATE('1234567890','$'||SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l),'$'))
         = 10 - LENGTH(SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l))
)
CYCLE str SET cycle_flag TO 'Y' DEFAULT 'N'
SELECT path||str FROM (
SELECT * FROM t ORDER BY len DESC,str DESC
)
WHERE ROWNUM=1;

PATH||STR
--------------------------------------------------------------------------------------------------------------------------------------
----------------
38(7),38(49),(3)82401,98(24)01,98(5)7601,98(2)57601,98457(6)01,984573601

已用时间:  00: 00: 50.37

〇〇 · 发表于 2010-9-2 12:27

原帖由 newkid 于 2010-9-1 23:10 发表
递归思路很简单，而且很模式化，写多了就像套路。

这才是设计模式,比编码更高一层

〇〇 · 发表于 2010-9-2 12:37

如果73楼不加and SUBSTR(str,p.pos,p.l)>'1'

PATH||STR
--------------------------------------------------------------------------------------------------------------------------------------
----------------
38(7),38(49),(3)82401,98(24)01,98(5)7601,98(2)57601,98457(6)01,984573601

已用时间: 00: 01: 36.87

〇〇 · 发表于 2010-9-2 13:26

居然有不同路径可以得到同一个值....
WITH p AS (
SELECT pos,l
FROM (SELECT ROWNUM pos FROM DUAL CONNECT BY ROWNUM<=10),(SELECT ROWNUM l FROM DUAL CONNECT BY ROWNUM<=3)
  )
,t (str,len,path) AS (
/*SELECT REPLACE(SYS_CONNECT_BY_PATH(n,','),',') ,LEVEL,''
  FROM (SELECT ROWNUM-1 n FROM DUAL CONNECT BY ROWNUM<=10)
START WITH n>0
CONNECT BY NOCYCLE LEVEL<=3
*/
select a,length(a),'' from (select to_char(level,'fm999') a from dual connect by level<=999)
where a<=9
or(a>=10 and a<=99 and mod(a,11)>0)
or(a>=100 and (substr(a,1,1)<>substr(a,2,1) and substr(a,1,1)<>substr(a,3,1) and substr(a,3,1)<>substr(a,2,1)))
UNION ALL
SELECT SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l)
   ,LENGTH(SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l))
   ,path||SUBSTR(str,1,pos-1)||'('||SUBSTR(str,pos,l)||')'||SUBSTR(str,pos+l)||','
FROM t,p
  WHERE pos+l-1<=t.len  and substr(SUBSTR(str,pos,l),-1)<>'0' and SUBSTR(str,p.pos,p.l)>'1'
  --and t.len-l+LENGTH(TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l)))<=10
  and
  case when SUBSTR(str,pos,l) <=3 then 0
   when SUBSTR(str,pos,l)<=31 then 1
   when SUBSTR(str,pos,l)<=316 then 2
   else 3
  end + len<=10
      AND LENGTH(TRANSLATE('1234567890','$'||SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l),'$'))
         = 10 - LENGTH(SUBSTR(str,1,pos-1)||TO_CHAR(SUBSTR(str,pos,l)*SUBSTR(str,pos,l))||SUBSTR(str,pos+l))
)
CYCLE str SET cycle_flag TO 'Y' DEFAULT 'N'
SELECT path||str FROM (
SELECT * FROM t ORDER BY len DESC,str DESC
)
WHERE ROWNUM=1;

PATH||STR
-----------------------------------------------------------------------------
----------------
38(7),38(49),(3)82401,98(24)01,98(5)7601,98(2)57601,98457(6)01,984573601

已用时间:  00: 00: 50.40

/*+rule*/

PATH||STR
---------------------------------------------------------------------------
----------------
38(7),38(49),38(24)01,(3)857601,9857(6)01,98(5)73601,98(2)573601,984573601

已用时间:  00: 00: 49.67

SQL> 19
19* FROM t,p
SQL> c/t,p/p,t
19* FROM p,t
SQL> /

PATH||STR
---------------------------------------------------------------------------------
----------------
38(7),38(49),(3)82401,98(24)01,98(5)7601,98(2)57601,98457(6)01,984573601

已用时间:  00: 00: 59.26

〇〇 · 发表于 2010-9-2 13:35

如果能证明3位数的平方都不合适...
SQL> 22
22* and
SQL> a l<3 and
22* andl<3 and
SQL> 22
22* andl<3 and
SQL> c/dl/d l
22* and l<3 and
SQL> /

PATH||STR
---------------------------------------------------------------------------
----------------
38(7),38(49),(3)82401,98(24)01,98(5)7601,98(2)57601,98457(6)01,984573601

已用时间: 00: 00: 37.00

lastwinner · 发表于 2010-9-2 16:54

原帖由〇〇于 10-9-2 13:26 发表
居然有不同路径可以得到同一个值....

ps：三位数里有满足题设条件的

with p as (select rownum-1 rn from dual connect by rownum<=10),
three_digits as (select replace(sys_connect_by_path(rn,','),',','') num  from p where level=3 start with rn>0 connect by nocycle rn<>prior rn  and level<=3)
select num, numnum from
(select num, power(num,2) numnum from three_digits)
where length(numnum)=11-length(translate('0123456789',numnum,'$'))
/

NUM    NUMNUM
------- --------
124       15376
126       15876
128       16384
134       17956
136       18496
137       18769
142       20164
147       21609
148       21904
152       23104
153       23409
154       23716
169       28561
172       29584
174       30276
175       30625
176       30976
178       31684
179       32041
186       34596
189       35721
193       37249
195       38025
196       38416
198       39204
203       41209
209       43681
213       45369
214       45796
217       47089
219       47961
248       61504
259       67081
267       71289
268       71824
269       72361
273       74529
281       78961
286       81796
287       82369
289       83521
295       87025
302       91204
304       92416
305       93025
309       95481
324    104976
328    107584
352    123904
364    132496
367    134689
374    139876
375    140625
397    157609
403    162409
405    164025
413    170569
416    173056
425    180625
456    207936
458    209764
463    214369
487    237169
504    254016
507    257049
508    258064
509    259081
529    279841
542    293764
564    318096
567    321489
571    326041
572    327184
574    329476
584    341056
589    346921
591    349281
593    351649
597    356409
598    357604
618    381924
621    385641
625    390625
629    395641
634    401956

NUM    NUMNUM
------- --------
637    405769
639    408321
645    416025
647    418609
651    423801
695    483025
705    497025
708    501264
709    502681
713    508369
724    524176
741    549081
759    576081
763    582169
783    613089
805    648025
807    651249
829    687241
839    703921
842    708964
843    710649
845    714025
852    725904
854    729316
867    751689
871    758641
872    760384
902    813604
903    815409
905    819025
916    839056
934    872356
945    893025
968    937024

已选择119行。

〇〇 · 发表于 2010-9-2 20:11

SQL> select num, numnum from
  2    (select num, power(num,2) numnum from (select to_char(level+99,'fm999') num from dual connect by level<=999-100))
  3    where length(numnum||num)=10-length(translate('0123456789','$'||numnum||num,'$'));

NUM    NUMNUM
---- ----------
209    43681
259    67081
567    321489
807    651249
854    729316

已用时间:  00: 00: 00.00

haiersknl · 发表于 2010-9-2 20:19

7楼的答案17 不是最优的，请你重新审视你的程序。

[精华] Puzzleup 2010 比赛快开始了，大家用SQL解答啊

浏览过的版块