|
本帖最后由 〇〇 于 2022-12-18 18:38 编辑
6人的穷举法
with recursive
a(a)as(values('A'),('B'),('C'),('D'),('E'),('F')),
b(a,b,c,d)as(select a.a,b.a,a.a||b.a,pow(10,row_number()over(order by a.a,b.a)-1)::bigint from a,a b where a.a<b.
t(a,b,c) as
(values
('A', 'B', 'C'),
('A', 'B', 'D'),
('A', 'B', 'E'),
('A', 'B', 'F'),
('A', 'C', 'D'),
('A', 'C', 'E'),
('A', 'C', 'F'),
('A', 'D', 'E'),
('A', 'D', 'F'),
('A', 'E', 'F'),
('B', 'C', 'D'),
('B', 'C', 'E'),
('B', 'C', 'F'),
('B', 'D', 'E'),
('B', 'D', 'F'),
('B', 'E', 'F'),
('C', 'D', 'E'),
('C', 'D', 'F'),
('C', 'E', 'F'),
('D', 'E', 'F')
),
v as(select t.a||t.b||t.c s,(pow(10,(ascii(t.a)-65))+pow(10,(ascii(t.b)-65))+pow(10,(ascii(t.c)-65)))::bigint b ,
(select sum(b.d) from b where instr(t.a||t.b||t.c,b.a) and instr(t.a||t.b||t.c,b.b))::bigint c from t)
select * from v;
┌─────────┬────────┬─────────────────┐
│ s │ b │ c │
│ varchar │ int64 │ int64 │
├─────────┼────────┼─────────────────┤
│ ABC │ 111 │ 100011 │
│ ABD │ **1 │ 1000** │
│ ABE │ 10011 │ 10001001 │
│ ABF │ 100011 │ 100010001 │
│ ACD │ 1** │ 1000000110 │
│ ACE │ **01 │ 1000000**0 │
│ ACF │ 100** │ 100000010010 │
│ ADE │ 11001 │ 1000000001100 │
│ ADF │ **001 │ 100000000**00 │
│ AEF │ 110001 │ 100000000011000 │
│ BCD │ 1110 │ 1001100000 │
│ BCE │ **10 │ 100**00000 │
│ BCF │ 100110 │ 100100100000 │
│ BDE │ 1**0 │ 1000011000000 │
│ BDF │ **010 │ 10000**000000 │
│ BEF │ 110010 │ 100000110000000 │
│ CDE │ 11100 │ **1000000000 │
│ CDF │ **100 │ **01000000000 │
│ CEF │ 1**00 │ 100110000000000 │
│ DEF │ 111000 │ 111000000000000 │
├─────────┴────────┴─────────────────┤
│ 20 rows 3 columns │
└────────────────────────────────────┘
with recursive
a(a)as(values('A'),('B'),('C'),('D'),('E'),('F')),
b(a,b,c,d)as(select a.a,b.a,a.a||b.a,pow(10,row_number()over(order by a.a,b.a)-1)::bigint from a,a b where a.a<b.a),
t(a,b,c) as
(values
('A', 'B', 'C'),
('A', 'B', 'D'),
('A', 'B', 'E'),
('A', 'B', 'F'),
('A', 'C', 'D'),
('A', 'C', 'E'),
('A', 'C', 'F'),
('A', 'D', 'E'),
('A', 'D', 'F'),
('A', 'E', 'F'),
('B', 'C', 'D'),
('B', 'C', 'E'),
('B', 'C', 'F'),
('B', 'D', 'E'),
('B', 'D', 'F'),
('B', 'E', 'F'),
('C', 'D', 'E'),
('C', 'D', 'F'),
('C', 'E', 'F'),
('D', 'E', 'F')
),
v as(select t.a||t.b||t.c s,(pow(10,(ascii(t.a)-65))+pow(10,(ascii(t.b)-65))+pow(10,(ascii(t.c)-65)))::bigint b ,
(select sum(b.d::hugeint) from b where instr(t.a||t.b||t.c,b.a) and instr(t.a||t.b||t.c,b.b)) c from t),
r as(select 1 lv, v.s,v.b,v.c from v where v.b=111
union all
select lv+1,r.s||v.s,r.b+v.b,r.c+v.c from v,r where lv<20 and substr(r.s,-3)<v.s and (r.b+v.b)::varchar not SIMILAR TO '.*(6).*' and (r.c+v.c)::varchar not SIMILAR TO '.*(3).*')
select max(len(s)) from r;
┌─────────────┐
│ max(len(s)) │
│ int64 │
├─────────────┤
│ 30 │
└─────────────┘
select s from r where len(s)=30 limit 4;
┌────────────────────────────────┐
│ s │
│ varchar │
├────────────────────────────────┤
│ ABCABDACEADFAEFBCFBDEBEFCDECDF │
│ ABCABDACFADEAEFBCEBDFBEFCDECDF │
│ ABCABEACDADFAEFBCFBDEBDFCDECEF │
│ ABCABEACFADEADFBCDBDFBEFCDECEF │
└────────────────────────────────┘
|
|