SQL大赛——5X5方格棋盘难题-原创手记-慕课网

“盛拓传媒杯”SQL大赛第一期答题，

本期题目：5X5方格棋盘难题

在5X5的方格棋盘中（如图），每行、列、斜线（斜线不仅仅包括对角线）最多可以放两个球，如何摆放才能放置最多的球，这样的摆法总共有几种？输出所有的摆法。

要求：用一句SQL实现。

输出格式：从方格棋盘第一行至第5行，每行从第一列到第5列依次输出，0表示不放球，1表示放球。例如：1001000000000000000000000。一行输出一个行号和一个解，按解所在的列字符串顺序从大到小排序。

数据库平台：适用Oracle、MS SQL Sever，版本（Oracle推荐10gr2（包含）以上版本、MS SQL Sever推荐2008版本）

我个人的答案如下：

--构造0、1选项

with test as

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

--构造每行排列组合

combostring as

(

select replace(sys_connect_by_path(bit,'#'),'#') combo

from test

where level=5

connect by level<=5

--把“10001”的字符串，构造成可计算的数字

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),

--根据题目进行逻辑求解

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

)

--列出符合要求的记录

select

combo

from allcombo

WHERE RANK=1

oraclesql数据库数据库