WITH
--每个位置放或不放
choice AS
(SELECT 1 c
FROM dual
UNION ALL
SELECT 0 c
FROM dual),
--5行
rows_level AS
(SELECT LEVEL l
FROM dual
CONNECT BY LEVEL = 5),
--5X5的格子,建立坐标
board AS
(SELECT /*+ materialize */
x.l AS x,
y.l AS y,
x.l + y.l AS z1,
x.l - y.l AS z2,
x.l + 10 * y.l AS n,
x.l * 5 + y.l AS P
FROM (SELECT LEVEL - 1 l
FROM dual
CONNECT BY LEVEL = 5) x,
(SELECT LEVEL - 1 l
FROM dual
CONNECT BY LEVEL = 5) y),
--每行最多放2个,列出所有行组合的情况，同时也排除了行
comb AS
(SELECT ROWNUM AS comb_id,
t1.c || t2.c || t3.c || t4.c || t5.c AS comb_value
FROM choice t1, choice t2, choice t3, choice t4, choice t5
WHERE t1.c + t2.c + t3.c + t4.c + t5.c = 2),
--列出摆放情况
placed_on AS
(SELECT /*+ materialize */
REPLACE(sys_connect_by_path(comb_value, ','), ',', '') temp_result
FROM comb
WHERE LEVEL = 5
CONNECT BY NOCYCLE /*PRIOR comb_id != comb_id
AND */LEVEL = 5),
--排除列
removed_exceed_col AS
(SELECT /*+ materialize */
a.temp_result
FROM placed_on a, rows_level b
GROUP BY a.temp_result
HAVING MAX(SUBSTR(a.temp_result, b.l + 0 * 5, 1) +
SUBSTR(a.temp_result, b.l + 1 * 5, 1) +
SUBSTR(a.temp_result, b.l + 2 * 5, 1) +
SUBSTR(a.temp_result, b.l + 3 * 5, 1) +
SUBSTR(a.temp_result, b.l + 4 * 5, 1)
) = 2),
--排除斜线
removed_exceed_bias AS
(SELECT /*+ materialize */
temp_result AS final_answer
FROM (SELECT a.temp_result,
b.z1,
b.z2,
SUM(SUBSTR(a.temp_result, b.p + 1, 1)) OVER(PARTITION BY a.temp_result, b.z1) v1,
SUM(SUBSTR(a.temp_result, b.p + 1, 1)) OVER(PARTITION BY a.temp_result, b.z2) v2
FROM removed_exceed_col a, board b)
GROUP BY temp_result
HAVING MAX(v1) = 2 AND MAX(v2) = 2)
--最终结果
SELECT RANK() OVER(ORDER BY final_answer DESC) rn, final_answer
FROM removed_exceed_bias
ORDER BY final_answer DESC
