〇〇 发表于 2022-11-27 09:58
从所有的4格组合中找出能筛选10个的with b(n,s) as(values(1,[1,2,3,4]),(2,[2,3,4,5]),(3,[6,7,8,9]),(4, ...
上面的逻辑有问题,改成下面的,速度很慢
with b(n,s) as(values
(1,[1,2,3,4]),
(2,[2,3,4,5]),
(3,[6,7,8,9]),
(4,[7,8,9,10]),
(5,[11,12,13,14]),
(6,[12,13,14,15]),
(7,[16,17,18,19]),
(8,[17,18,19,20]),
(9,[21,22,23,24]),
(10,[22,23,24,25]),
(11,[1,6,11,16]),
(12,[6,11,16,21]),
(13,[2,7,12,17]),
(14,[7,12,17,22]),
(15,[3,8,13,18]),
(16,[8,13,18,23]),
(17,[4,9,14,19]),
(18,[9,14,19,24]),
(19,[5,10,15,20]),
(20,[10,15,20,25])
),
a as (select i from generate_series(1,25)t(i)),
t1 as(
select row_number()over()rn,[a.i,b.i,c.i,d.i]q from a,a b,a c,a d
where a.i>b.i and b.i>c.i and c.i>d.i), --select count() from t1
t as (select t1.rn,unnest(q)i from t1),
r as(
select t.rn,count(*)cnt from b,t
where not exists(select 1 from t t2 where t.rn=t2.rn and t2.i in (select unnest(s)))group by t.rn
--select t.rn,count(t.i)cnt from t,b where list_has(b.s,t.i)group by t.rn
)
--select cnt,count(*) from r group by cnt;
--select count(*) from r where cnt=10;
select t1.* from r,t1 where cnt=40 and t1.rn=r.rn limit 10;
|