# “盛拓传媒杯”SQL数据库编程大赛第一期代码

WITH
EVERY_CELL--每个格子的两种情况
AS
( SELECT 1 P1 FROM DUAL
UNION
SELECT 0 FROM DUAL
),
EVERY_ROW--每行的情况
AS
(
SELECT A.P1 P1,B.P1 P2,C.P1 P3,D.P1 P4,E.P1 P5
FROM EVERY_CELL A,EVERY_CELL B,EVERY_CELL C,EVERY_CELL D,EVERY_CELL E
WHERE A.P1+B.P1+C.P1+D.P1+E.P1<=2
),
RESULT--五行的结果
AS
(
SELECT A1.P1 W1,A1.P2 W2,A1.P3 W3,A1.P4 W4,A1.P5 W5,
A2.P1 W6,A2.P2 W7,A2.P3 W8,A2.P4 W9,A2.P5 W10,
A3.P1 W11,A3.P2 W12,A3.P3 W13,A3.P4 W14,A3.P5 W15,
A4.P1 W16,A4.P2 W17,A4.P3 W18,A4.P4 W19,A4.P5 W20,
A5.P1 W21,A5.P2 W22,A5.P3 W23,A5.P4 W24,A5.P5 W25
FROM EVERY_ROW A1,EVERY_ROW A2,EVERY_ROW A3,EVERY_ROW A4,EVERY_ROW A5
WHERE A1.P1+A2.P1+A3.P1+A4.P1+A5.P1<=2
AND A1.P2+A2.P2+A3.P2+A4.P2+A5.P2<=2
AND A1.P3+A2.P3+A3.P3+A4.P3+A5.P3<=2
AND A1.P4+A2.P4+A3.P4+A4.P4+A5.P4<=2
AND A1.P5+A2.P5+A3.P5+A4.P5+A5.P5<=2
AND A1.P1+A2.P2+A3.P3+A4.P4+A5.P5<=2
AND A1.P2+A2.P3+A3.P4+A4.P5<=2
AND A1.P3+A2.P4+A3.P5<=2
AND A1.P4+A2.P5<=2
AND A2.P1+A3.P2+A4.P3+A5.P4<=2
AND A3.P1+A4.P2+A5.P3<=2
AND A4.P1+A5.P2<=2
AND A1.P2+A2.P1<=2
AND A1.P3+A2.P2+A3.P1<=2
AND A1.P4+A2.P3+A3.P2+A4.P1<=2
AND A1.P5+A2.P4+A3.P3+A4.P2+A5.P1<=2
AND A2.P5+A3.P4+A4.P3+A5.P2<=2
AND A3.P5+A4.P4+A5.P3<=2
AND A4.P5+A5.P4<=2
),
RESULT_MAX--求出符合条件的结果最大值
AS
(
SELECT MAX(W1+W2+W3+W4+W5+W6+W7+W8+W9+W10+W11+W12+W13+W14+W15+W16+W17+W18+W19+W20+W21+W22+W23+W24+W25) VAL FROM RESULT
),
ISRESULT--排序结果
AS
(
SELECT W1,W2,W3,W4,W5,W6,W7,W8,W9,W10,W11,W12,W13,W14,W15,W16,W17,W18,W19,W20,W21,W22,W23,W24,W25 FROM RESULT,RESULT_MAX
WHERE W1+W2+W3+W4+W5+W6+W7+W8+W9+W10+W11+W12+W13+W14+W15+W16+W17+W18+W19+W20+W21+W22+W23+W24+W25=RESULT_MAX.VAL
ORDER BY W1 DESC,W2 DESC,W3 DESC,W4 DESC,W5 DESC,W6 DESC,W7 DESC,W8 DESC,W9 DESC,W10 DESC,W11 DESC,W12 DESC,W13 DESC,W14 DESC,W15 DESC,W16 DESC,W17 DESC,W18 DESC,W19 DESC,W20 DESC,W21 DESC,W22 DESC,W23 DESC,W24 DESC,W25 DESC
)
SELECT ROWNUM,IR.*FROM ISRESULT IR

• 博文量
123
• 访问量
180242