PUZZLEUP 2017

lugionline · 发表于 2017-8-17 21:01

看 MatrixPower，这不是只会Sort的PL/SQL程序员可以理解的

算 8x8也毫无压力阿

让我们期待newkid的SQL版矩阵乘法算8x8

不是机器吐血就是键盘敲到吐血

newkid · 发表于 2017-8-17 22:10

我这个SQL就专门解决这个问题，对丧心病狂的的NP, NPC没有兴趣

WITH t (colors,cnt) AS (
SELECT CAST(c AS VARCHAR2(16)) colors
   ,1 cnt
  FROM (SELECT LEVEL c FROM DUAL CONNECT BY LEVEL<=3)
UNION ALL
SELECT colors||c,cnt+1
  FROM t,(SELECT LEVEL c FROM DUAL CONNECT BY LEVEL<=3)
WHERE cnt<16
   AND (cnt+1 in (2,3,4) AND c<>substr(colors,-1)
         OR
         cnt+1 in (5,9,13) AND c<>substr(colors,cnt+1-4,1)
         OR c NOT IN (substr(colors,-1),substr(colors,cnt+1-4,1))
         )
)
SELECT COUNT(*) FROM t WHERE cnt=16 AND INSTR(colors,'1')>0 AND INSTR(colors,'2')>0 AND INSTR(colors,'3')>0;

COUNT(*)
---------
   7806

随机挑两个徽章送给加菲猫。

solomon_007 · 发表于 2017-8-17 22:55

本帖最后由 solomon_007 于 2017-8-18 00:01 编辑

lugionline 发表于 2017-8-17 21:01
看 MatrixPower，这不是只会Sort的PL/SQL程序员可以理解的

你是火星人！

solomon_007 · 发表于 2017-8-17 23:02

newkid 发表于 2017-8-17 22:10
我这个SQL就专门解决这个问题，对丧心病狂的的NP, NPC没有兴趣

WITH t (colors,cnt) AS (

  CTE的条件判断，这回和你的还有点像。。。

  章章，多谢！

〇〇 · 发表于 2017-8-18 07:45

newkid 发表于 2017-8-17 22:10
我这个SQL就专门解决这个问题，对丧心病狂的的NP, NPC没有兴趣

WITH t (colors,cnt) AS (

硬编码，比猫的少用了一个表，

〇〇 · 发表于 2017-8-18 07:49

本帖最后由〇〇于 2017-8-18 07:56 编辑

newkid 发表于 2017-8-17 22:10
我这个SQL就专门解决这个问题，对丧心病狂的的NP, NPC没有兴趣

WITH t (colors,cnt) AS (

奇怪，在sqlite下出错了
sqlite> WITH t (colors,cnt) AS (
...> SELECT CAST(c AS VARCHAR2(16)) colors
...>    ,1 cnt
...> FROM (SELECT 1 c union select 2 union select 3)
...> UNION ALL
...> SELECT colors||c,cnt+1
...> FROM t,(SELECT 1 c union select 2 union select 3)
...> WHERE cnt<16
...>       AND (cnt+1 in (2,3,4) AND c<>substr(colors,-1)
...>          OR
...>          cnt+1 in (5,9,13) AND c<>substr(colors,cnt+1-4,1)
...>          OR c NOT IN (substr(colors,-1),substr(colors,cnt+1-4,1))
...>          )
...> )
...> SELECT COUNT(*) FROM t WHERE cnt=16 AND INSTR(colors,'1')>0 AND INSTR(colors,'2')>0 AND INSTR(colors,'3')>0;
Run Time: real 5.172 user 3.875000 sys 0.562500
Error: database or disk is full

原来是没有自动类型转换
sqlite> WITH t (colors,cnt) AS (
...> SELECT CAST(c AS VARCHAR(16)) colors
...>    ,1 cnt
...> FROM (SELECT '1' c union select '2' union select '3')
...> UNION ALL
...> SELECT colors||c,cnt+1
...> FROM t,(SELECT '1' c union select '2' union select '3')
...> WHERE cnt<16
...>       AND (cnt+1 in (2,3,4) AND c<>substr(colors,-1)
...>          OR
...>          cnt+1 in (5,9,13) AND c<>substr(colors,cnt+1-4,1)
...>          OR c NOT IN (substr(colors,-1),substr(colors,cnt+1-4,1))
...>          )
...> )
...> SELECT COUNT(*) FROM t WHERE cnt=16 AND INSTR(colors,'1')>0 AND INSTR(colors,'2')>0 AND INSTR(colors,'3')>0;
Run Time: real 0.078 user 0.078125 sys 0.000000

〇〇 · 发表于 2017-8-18 07:54

〇〇发表于 2017-8-18 07:49
奇怪，在sqlite下出错了
sqlite> WITH t (colors,cnt) AS (
...> SELECT CAST(c AS VARCHAR2(16)) c ...

猫的
with n as(select 1 n union all select n+1 from n where n<4),
t as (select 'R' color
            union all
         select 'Y'
            union all
         select 'B' ),
s as (select n.n*4-4+n1.n n,
                  n.n x,
                  n1.n y
from n,n n1),
r(len,str,x,y) as (select 1,color,1,1
                  from t
               union all
               select r.len + 1,
                     r.str||t.color,
                     s.x,
                     s.y
                  from r,t,s
               where r.len < 4*4
                  and r.len + 1 = s.n
                  and t.color not in (select substr(r.str,s2.n,1) node
                                       from s s2
                                       --where abs(s.x - s2.x) + abs(s.y - s2.y) = 1
                                       where ((s.x - s2.x = 1 and s2.y = s.y ) or (s.y - s2.y = 1 and s.x = s2.x))
                                       and s2.n < r.len + 1
                                       --xxx where s2.n=r.len or s2.n=r.len-4+1
                                          )and substr(r.str,1,1) = 'B' --随便取一个颜色
               )
select count(str)*3
from r
where len = 4*4
  and instr(r.str,'R') > 0
  and instr(r.str,'Y') > 0
  and instr(r.str,'B') > 0
;
Run Time: real 0.266 user 0.265625 sys 0.000000

〇〇 · 发表于 2017-8-18 07:59

〇〇发表于 2017-8-18 07:49
奇怪，在sqlite下出错了
sqlite> WITH t (colors,cnt) AS (
...> SELECT CAST(c AS VARCHAR2(16)) c ...

*3
sqlite> WITH t (colors,cnt) AS (
...> SELECT CAST('1' AS VARCHAR(16)) colors
...>    ,1 cnt
...> UNION ALL
...> SELECT colors||c,cnt+1
...> FROM t,(SELECT '1' c union select '2' union select '3')
...> WHERE cnt<16
...>       AND (cnt+1 in (2,3,4) AND c<>substr(colors,-1)
...>          OR
...>          cnt+1 in (5,9,13) AND c<>substr(colors,cnt+1-4,1)
...>          OR c NOT IN (substr(colors,-1),substr(colors,cnt+1-4,1))
...>          )
...> )
...> SELECT COUNT(*)*3 FROM t WHERE cnt=16 AND INSTR(colors,'1')>0 AND INSTR(colors,'2')>0 AND INSTR(colors,'3')>0;
7806
Run Time: real 0.031 user 0.031250 sys 0.000000

newkid · 发表于 2017-8-23 22:08

#3 FOUR DIGITS

A number has distinct digits and for its any four consecutive digits, the multiplication of the two digits in the middle is greater than the sum of the four digits.

What is the greatest such number?

一个数的每位数各不相同，对于其任意四个连续的数字，中间两位数的乘积大于四位数之和。

最大一个这样的数是多少？

我感觉加菲猫又要拿章章了。

solomon_007 · 发表于 2017-8-23 23:02

newkid 发表于 2017-8-23 22:08
#3 FOUR DIGITS

A number has distinct digits and for its any four consecutive digits, the multipli ...

这么简单啊。。。

SQL>
SQL> with t as (select level-1 n from dual connect by level <=10)
  2  select max(t1.n||t2.n||t3.n||t4.n)
  3  from t t1,t t2,t t3,t t4
  4  where t2.n*t3.n > t1.n+t2.n+t3.n+t4.n
  5 and t1.n > t2.n
  6 and t2.n > t3.n
  7 and t3.n > t4.n
  8  /
MAX(T1.N||T2.N||T3.N||T4.N)
--------------------------------------------------------------------------------
9876

[每日一题] PUZZLEUP 2017