puzzleup 2020 11月开始

solomon_007

# 1


--F 点只许过一次：
SQL>
SQL> with t_edge(eno,start_point,end_point) as
  2  ( select 1,'A','B' from dual
  3    union all
  4    select 2,'B','C' from dual
  5    union all
  6    select 3,'C','A' from dual
  7    union all
  8    select 4,'A','D' from dual
  9    union all
10    select 5,'D','C' from dual
11    union all
12    select 6,'B','D' from dual
13    union all
14    select 7,'C','E' from dual
15    union all
16    select 8,'D','E' from dual
17    union all
18    select 9,'C','F' from dual
19    union all
20    select 10,'D','F' from dual
21    union all
22    select 11,'E','F' from dual),
23  s_edge (eno,start_point,end_point) as
24  (
25    select eno,start_point,end_point
26      from t_edge
27     union all
28    select eno,end_point,start_point
29      from t_edge
30  ),
31  s_cte(start_point,end_point,edge_list,point_list,edge_value_sum) as
32  (
33    select start_point,
34           end_point,
35           cast(eno as varchar2(500)),
36           start_point||end_point,
37           power(2,eno)
38      from s_edge
39     where start_point = 'A'
40     union all
41    select
42           se.start_point,
43           se.end_point,
44           sc.edge_list||'->'||se.eno,
45           sc.point_list||'->'||se.start_point||se.end_point,
46           sc.edge_value_sum + power(2,se.eno)
47      from s_cte sc, s_edge se
48     where sc.end_point = se.start_point
49       and bitand(sc.edge_value_sum,power(2,se.eno))=0
50  )
51  select count(*)
52    from s_cte
53   where end_point = 'F'
54     and regexp_count(point_list,'F',1) = 1
55  /

  COUNT(*)
----------
       132

〇〇

用bitand去重复很newkid啊

solomon_007

〇〇 发表于 2020-11-5 19:55
用bitand去重复很newkid啊

  依葫芦画瓢

newkid

给猫转了给章。都一年多没有动过徽章了，成为古董了。

我的写法大同小异，但是你碰到了F就没有必要再继续：

WITH v AS (
SELECT 'A' v1,'B' v2 FROM DUAL
UNION ALL SELECT 'B' v1,'C' v2 FROM DUAL
UNION ALL SELECT 'C' v1,'D' v2 FROM DUAL
UNION ALL SELECT 'A' v1,'C' v2 FROM DUAL
UNION ALL SELECT 'A' v1,'D' v2 FROM DUAL
UNION ALL SELECT 'B' v1,'D' v2 FROM DUAL
UNION ALL SELECT 'D' v1,'E' v2 FROM DUAL
UNION ALL SELECT 'E' v1,'F' v2 FROM DUAL
UNION ALL SELECT 'C' v1,'F' v2 FROM DUAL
UNION ALL SELECT 'C' v1,'E' v2 FROM DUAL
UNION ALL SELECT 'D' v1,'F' v2 FROM DUAL
)
,v2 as (
select v.*,power(2,rownum-1) as e from v
)
,v3 as (
select * from v2 union all select v2,v1,e from v2
)
,t(v1,v2,bits,path) as (
select v1,v2,e,cast(v1||v2 as varchar2(60)) from v3 where v1='A'
UNION ALL
select v3.v1,v3.v2,t.bits+v3.e,t.path||','||v3.v1||v3.v2
  from t,v3
where t.v2<>'F' and bitand(t.bits,v3.e)=0
      and t.v2=v3.v1
)
select path from t where v2='F';

solomon_007

多谢NEWKID的章章。
是的，更正一下，碰到F就结束

SQL>
SQL>  with t_edge(eno,start_point,end_point) as
  2    ( select 1,'A','B' from dual
  3      union all
  4      select 2,'B','C' from dual
  5      union all
  6      select 3,'C','A' from dual
  7      union all
  8      select 4,'A','D' from dual
  9      union all
10      select 5,'D','C' from dual
11      union all
12      select 6,'B','D' from dual
13      union all
14      select 7,'C','E' from dual
15      union all
16      select 8,'D','E' from dual
17      union all
18      select 9,'C','F' from dual
19      union all
20      select 10,'D','F' from dual
21      union all
22      select 11,'E','F' from dual),
23    s_edge (eno,start_point,end_point) as
24    (
25      select eno,start_point,end_point
26        from t_edge
27       union all
28      select eno,end_point,start_point
29        from t_edge
30    ),
31    s_cte(start_point,end_point,edge_list,point_list,edge_value_sum) as
32    (
33      select start_point,
34             end_point,
35             cast(eno as varchar2(500)),
36             start_point||end_point,
37             power(2,eno)
38        from s_edge
39       where start_point = 'A'
40       union all
41      select
42             se.start_point,
43             se.end_point,
44             sc.edge_list||'->'||se.eno,
45             sc.point_list||'->'||se.start_point||se.end_point,
46             sc.edge_value_sum + power(2,se.eno)
47        from s_cte sc, s_edge se
48       where sc.end_point = se.start_point
49         and bitand(sc.edge_value_sum,power(2,se.eno))=0
50         and sc.end_point <> 'F'
51    )
52    select count(*)
53      from s_cte
54     where end_point = 'F'
55       --and regexp_count(point_list,'F',1) = 1
56  /

  COUNT(*)
----------
       132         

newkid

#2 TIME DIFFERENCES

17 May 1989 - 23:46 (17/05/89 - 23:46) can be numerically written as 1705892346, where each digit from 0 to 9 is used exactly once. What is the maximum numerical difference between two different time information having this property?

Note: 24-hour format is used therefore the time part is between 00:00 and 23:59.

1989年5月17日-23:46(17/05/89-23:46)在数值上可以写成1705892346，其中从0到9的每一个数字都只用一次。两种具有这种性质的不同时间信息之间的最大的差值是多少？

注：采用24小时制，因此时间部分是在00:00和23:59之间。

solomon_007

# 2

不是很严格，严格应该还要验证有效日期，比如2月不能又30，31号等，但下面的结果应该是对的，因为最大最小日期有效。


SQL> with t_dd as (select dd,power(2,substr(dd,1,1)) d1_v,power(2,substr(dd,2,1)) d2_v
  2                  from (select lpad(level,2,'0') dd from dual connect by level <=31)
  3                 where substr(dd,1,1) <> substr(dd,2,1)),
  4       t_mm as (select mm,power(2,substr(mm,1,1)) m1_v,power(2,substr(mm,2,1)) m2_v
  5                 from (select lpad(level,2,'0') mm from dual connect by level <=12)
  6                where substr(mm,1,1) <> substr(mm,2,1)),
  7       t_yy as (select yy,power(2,substr(yy,1,1)) y1_v,power(2,substr(yy,2,1)) y2_v
  8                 from (select lpad(level,2,'0') yy from dual connect by level <=99)
  9                where substr(yy,1,1)<> substr(yy,2,1)),
10       t_hh as (select hh,power(2,substr(hh,1,1)) h1_v,power(2,substr(hh,2,1)) h2_v
11                  from (select lpad(level,2,'0') hh from dual connect by level <=23)
12                 where substr(hh,1,1) <> substr(hh,2,1)),
13       t_mi as (select mi,power(2,substr(mi,1,1)) i1_v,power(2,substr(mi,2,1)) i2_v
14                  from (select lpad(level,2,'0') mi from dual connect by level <=59)
15                 where substr(mi,1,1)<> substr(mi,2,1))
16  select min(to_number(dd||mm||yy||hh||mi)) min_num,max(to_number(dd||mm||yy||hh||mi)) max_num,
17         max(to_number(dd||mm||yy||hh||mi)) - min(to_number(dd||mm||yy||hh||mi)) diff_num
18    from t_dd,t_mm,t_yy,t_hh,t_mi
19   where d1_v + d2_v + m1_v + m2_v + y1_v + y2_v + h1_v + h2_v + i1_v + i2_v = power(2,10)-1
20  /

   MIN_NUM    MAX_NUM   DIFF_NUM
---------- ---------- ----------
1406782359 2908761543 1501979184

solomon_007

# 2
--严格的判断了日期有效性
SQL> with function f_date (p_str in varchar2)
  2       return int
  3       is
  4         l_date date;
  5       begin
  6         l_date := to_date(p_str,'ddmmyy');
  7         return 1;
  8       exception when others then
  9         return 0;
10       end;
11       t_dd as (select dd,power(2,substr(dd,1,1)) d1_v,power(2,substr(dd,2,1)) d2_v
12                  from (select lpad(level,2,'0') dd from dual connect by level <=31)
13                 where substr(dd,1,1) <> substr(dd,2,1)),
14       t_mm as (select mm,power(2,substr(mm,1,1)) m1_v,power(2,substr(mm,2,1)) m2_v
15                 from (select lpad(level,2,'0') mm from dual connect by level <=12)
16                where substr(mm,1,1) <> substr(mm,2,1)),
17       t_yy as (select yy,power(2,substr(yy,1,1)) y1_v,power(2,substr(yy,2,1)) y2_v
18                 from (select lpad(level,2,'0') yy from dual connect by level <=99)
19                where substr(yy,1,1)<> substr(yy,2,1)),
20       t_hh as (select hh,power(2,substr(hh,1,1)) h1_v,power(2,substr(hh,2,1)) h2_v
21                  from (select lpad(level,2,'0') hh from dual connect by level <=23)
22                 where substr(hh,1,1) <> substr(hh,2,1)),
23       t_mi as (select mi,power(2,substr(mi,1,1)) i1_v,power(2,substr(mi,2,1)) i2_v
24                  from (select lpad(level,2,'0') mi from dual connect by level <=59)
25                 where substr(mi,1,1)<> substr(mi,2,1))
26  select min(to_number(dd||mm||yy||hh||mi)) min_num,max(to_number(dd||mm||yy||hh||mi)) max_num,
27         max(to_number(dd||mm||yy||hh||mi)) - min(to_number(dd||mm||yy||hh||mi)) diff_num
28    from t_dd,t_mm,t_yy,t_hh,t_mi
29   where d1_v + d2_v + m1_v + m2_v + y1_v + y2_v + h1_v + h2_v + i1_v + i2_v = power(2,10)-1
30     and f_date(dd||mm||yy) = 1;
31  /

   MIN_NUM    MAX_NUM   DIFF_NUM
---------- ---------- ----------
1406782359 2908761543 1501979184

newkid

已转徽章。

判断有效日期在这里很容易，因为数字不能重复，最难搞的闰年 0229 就被排除了，11月也排除，剩下就是几个日期枚举一下：

WITH d AS (
SELECT LEVEL-1 n, POWER(2,LEVEL-1) b FROM DUAL CONNECT BY LEVEL<=10
)
,d2 AS (
SELECT d1.n||d2.n n2, d1.b+d2.b b2 FROM d d1, d d2 WHERE d1.n<>d2.n
)
,ddmmyyhhmi AS (
SELECT dd.n2||mm.n2||yy.n2||hh.n2||mi.n2 t
  FROM d2 dd,d2 mm,d2 yy,d2 hh,d2 mi
WHERE BITAND(dd.b2,mm.b2)=0 AND BITAND(dd.b2,yy.b2)=0 AND BITAND(dd.b2,hh.b2)=0 AND BITAND(dd.b2,mi.b2)=0
       AND BITAND(mm.b2,yy.b2)=0 AND BITAND(mm.b2,hh.b2)=0 AND BITAND(mm.b2,mi.b2)=0
       AND BITAND(yy.b2,hh.b2)=0 AND BITAND(yy.b2,mi.b2)=0
       AND BITAND(hh.b2,mi.b2)=0
       AND hh.n2 BETWEEN '00' AND '23'
       AND mi.n2 BETWEEN '00' AND '59'
       AND mm.n2 BETWEEN '01' AND '12'
       AND dd.n2 BETWEEN '01' AND '31'
       AND mm.n2||dd.n2 NOT IN ('0231','0431','0631','0931')
)
SELECT MIN(t),MAX(t) FROM ddmmyyhhmi;

solomon_007

你的只搞一个数字的组合简洁多了！