SQL Contest Title: 5x5 Checker Puzzle

"Sheng Media Cup" SQL contest, the first issue of the answer,

Current issue: 5x5 checkers Puzzle

In the 5x5 checkerboard (as pictured), each row, column, slash (slash not only include diagonal) can put up to two balls, how to put the most ball, so there are a total of several pendulum method? Output all the pendulum methods.

Requirements: Implemented in a single SQL sentence.

Output format: From the first row to the 5th row of the checkerboard, each row from the first column to the 5th column output, 0 means not to put the ball, 1 means the ball. For example: 1001000000000000000000000. One line outputs a row number and a solution sorted from large to small in the order of the column string in which the solution is located.

Database platform: Applicable to Oracle, MS SQL Sever, version (Oracle recommended 10GR2 (inclusive) version, MS SQL Sever recommended version 2008)

My personal answer is as follows:

--Constructs 0, 1 options

With Test as

(SELECT ' 1 ' bit from dual union select ' 0 ' dual),

--Construct each row arrangement group

Combostring as

(

Select Replace (Sys_connect_by_path (bit, ' # '), ' # ') combo

From Test

where level=5

Connect by level<=5

),

--Putting the string of "10001" into a computable number

Combo as

(select substr (combo,1,1) b1,substr (combo,2,1) b2,substr (combo,3,1) b3,substr (combo,4,1) b4,substr (combo,5,1) B5

From Combostring),

--Logical solution based on the topic

Allcombo as

(

Select C1.b1| | c1.b2| | c1.b3| | c1.b4| | c1.b5| | ' '|| c2.b1| | c2.b2| | c2.b3| | c2.b4| | c2.b5| | ' '||

c3.b1| | c3.b2| | c3.b3| | c3.b4| | c3.b5| | ' '|| c4.b1| | c4.b2| | c4.b3| | c4.b4| | c4.b5| | ' '|| c5.b1| | c5.b2| | c5.b3| | c5.b4| | C5.B5 Combo,

RANK () over (order by

c1.b1+c1.b2+c1.b3+c1.b4+c1.b5+ c2.b1+c2.b2+c2.b3+c2.b4+c2.b5+ c3.b1+c3.b2+c3.b3+c3.b4+c3.b5+ c4.b1+c4.b2+c4.b3+ c4.b4+c4.b5+ c5.b1+c5.b2+c5.b3+c5.b4+c5.b5 DESC) RANK

From combo c1,combo C2,combo C3,combo c4,combo c5

where c1.b1+c2.b1+c3.b1+c4.b1+c5.b1<=2

and c1.b2+c2.b2+c3.b2+c4.b2+c5.b2<=2

and c1.b3+c2.b3+c3.b3+c4.b3+c5.b3<=2

and c1.b4+c2.b4+c3.b4+c4.b4+c5.b4<=2

and c1.b5+c2.b5+c3.b5+c4.b5+c5.b5<=2

and c1.b1+c1.b2+c1.b3+c1.b4+c1.b5<=2

and c2.b1+c2.b2+c2.b3+c2.b4+c2.b5<=2

and c3.b1+c3.b2+c3.b3+c3.b4+c3.b5<=2

and c4.b1+c4.b2+c4.b3+c4.b4+c4.b5<=2

and c5.b1+c5.b2+c5.b3+c5.b4+c5.b5<=2

and c1.b1+c2.b2+c3.b3+c4.b4+c5.b5<=2

and c1.b5+c2.b4+c3.b3+c4.b2+c5.b1<=2

and c1.b2+c2.b3+c3.b4+c4.b5<=2

and c2.b1+c3.b2+c4.b3+c5.b4<=2

and c1.b3+c2.b4+c3.b5<=2

and c3.b1+c4.b2+c5.b3<=2

and c1.b4+c2.b3+c3.b2+c4.b1<=2

and c2.b5+c3.b4+c4.b3+c5.b2<=2

and c1.b3+c2.b2+c3.b1<=2

and c3.b5+c4.b4+c5.b3<=2

)

--list records that meet the requirements

Select

Combo

From Allcombo

WHERE rank=1

This article from the "Life is a drunken" blog, please be sure to retain this source http://baoqiangwang.blog.51cto.com/1554549/514779

See more highlights of this column: http://www.bianceng.cn/database/basis/