var N number
exec :N :=2
WITH
linesrc as (
select col1||col2||col3||col4||col5 strings,
col1+col2+col3+col4+col5 balls,
r.*
from (
select
mod(floor(rownum/16),2) col1 ,
mod(floor(rownum/8),2) col2 ,
mod(floor(rownum/4),2) col3 ,
mod(floor(rownum/2),2) col4 ,
mod(floor(rownum),2) col5
from dual
connect by rownum <=32
) r
where col1+col2+col3+col4+col5<=:N
ORDER BY 1 desc
),
probed_result as (
select
max(line1.balls + line2.balls + line3.balls + line4.balls + line5.balls) probed_max_balls
from
linesrc line1,
linesrc line2,
linesrc line3,
linesrc line4,
linesrc line5
where
line1.col1 + line2.col1 + line3.col1+ line4.col1+ line5.col1 <=:N
and line1.col2 + line2.col2 + line3.col2+ line4.col2+ line5.col2 <=:N
and line1.col3 + line2.col3 + line3.col3+ line4.col3+ line5.col3 <=:N
and line1.col4 + line2.col4 + line3.col4+ line4.col4+ line5.col4 <=:N
and line1.col5 + line2.col5 + line3.col5+ line4.col5+ line5.col5 <=:N
and line1.col1 + line2.col2 + line3.col3+ line4.col4+ line5.col5 <=:N
and line1.col2 + line2.col3 + line3.col4+ line4.col5 <=:N
and line1.col3 + line2.col4 + line3.col5<=:N
and line1.col4 + line2.col5 <=:N
and line2.col1 + line3.col2 + line4.col3+ line5.col4 <=:N
and line3.col1 + line4.col2 + line5.col3 <=:N
and line4.col1 + line5.col2 <=:N
and line1.col5 + line2.col4 + line3.col3+ line4.col2+ line5.col1 <=:N
and line1.col4 + line2.col3 + line3.col2+ line4.col1 <=:N
and line1.col3 + line2.col2 + line3.col1 <=:N
and line1.col2 + line2.col1 <=:N
and line2.col5 + line3.col4 + line4.col3+ line5.col2<=:N
and line3.col5 + line4.col4 + line5.col3<=:N
and line4.col5 + line5.col4 <=:N
and rownum < power(10,:N)*2
),
probed_linesrc AS (
select *
from linesrc,probed_result
where balls>= probed_max_balls-4*:N -- 最少要probed_max_balls才能符合要求，但是每一行最多N个ball, 所以每一行至少probed_max_balls-4*N
),
solution as (
select /*+ materialize */
line1.strings || line2.strings || line3.strings || line4.strings || line5.strings result ,
line1.balls + line2.balls + line3.balls + line4.balls + line5.balls total_balls
from
probed_linesrc line1,
probed_linesrc line2,
probed_linesrc line3,
probed_linesrc line4,
probed_linesrc line5
where
line1.col1 + line2.col1 + line3.col1+ line4.col1+ line5.col1 <=:N
and line1.col2 + line2.col2 + line3.col2+ line4.col2+ line5.col2 <=:N
and line1.col3 + line2.col3 + line3.col3+ line4.col3+ line5.col3 <=:N
and line1.col4 + line2.col4 + line3.col4+ line4.col4+ line5.col4 <=:N
and line1.col5 + line2.col5 + line3.col5+ line4.col5+ line5.col5 <=:N
and line1.col1 + line2.col2 + line3.col3+ line4.col4+ line5.col5 <=:N
and line1.col2 + line2.col3 + line3.col4+ line4.col5 <=:N
and line1.col3 + line2.col4 + line3.col5<=:N
and line1.col4 + line2.col5 <=:N
and line2.col1 + line3.col2 + line4.col3+ line5.col4 <=:N
and line3.col1 + line4.col2 + line5.col3 <=:N
and line4.col1 + line5.col2 <=:N
and line1.col5 + line2.col4 + line3.col3+ line4.col2+ line5.col1 <=:N
and line1.col4 + line2.col3 + line3.col2+ line4.col1 <=:N
and line1.col3 + line2.col2 + line3.col1 <=:N
and line1.col2 + line2.col1 <=:N
and line2.col5 + line3.col4 + line4.col3+ line5.col2<=:N
and line3.col5 + line4.col4 + line5.col3<=:N
and line4.col5 + line5.col4 <=:N
)
select rownum r,result from solution
where total_balls = (select max(total_balls) from solution)