oracle数据库大赛,分享一下SQL数据库编程大赛第一期我的解法-CSDN博客

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