刑侦高考：如何用SQL解决环环相扣的刑侦推理问题

高考期间看到一个朋友在朋友圈里面贴了个图，上面是2018年刑侦科推理试题，看了一下题目，这些题目都是彼此依赖，很难找到一个题目作为入手点可以进一步分析，因此可能需要用纸笔配合大量的假设和试错才能继续完成。

原题如下，大家可以先烧烧脑，再来看解决方案：

这种问题其实是SQL擅长的领域，于是一时手痒，写了一个SQL语句：

SQL> WITH T AS 2 (SELECT ASCII('A') R FROM DUAL 3 UNION ALL 4 SELECT ASCII('B') FROM DUAL 5 UNION ALL 6 SELECT ASCII('C') FROM DUAL 7 UNION ALL 8 SELECT ASCII('D') FROM DUAL), 9 RESULT AS 10 (SELECT T1.R R1, T2.R R2, T3.R R3, T4.R R4, T5.R R5, T6.R R6, T7.R R7, T8.R R8, T9.R R9, T10.R R10, 11 DECODE(T1.R, ASCII('A'), 1, 0) + DECODE(T2.R, ASCII('A'), 1, 0) + DECODE(T3.R, ASCII('A'), 1, 0) + DECODE(T4.R, ASCII('A'), 1, 0) 12 + DECODE(T5.R, ASCII('A'), 1, 0) + DECODE(T6.R, ASCII('A'), 1, 0) + DECODE(T7.R, ASCII('A'), 1, 0) 13 + DECODE(T8.R, ASCII('A'), 1, 0) + DECODE(T9.R, ASCII('A'), 1, 0) + DECODE(T10.R, ASCII('A'), 1, 0) A, 14 DECODE(T1.R, ASCII('B'), 1, 0) + DECODE(T2.R, ASCII('B'), 1, 0) + DECODE(T3.R, ASCII('B'), 1, 0) + DECODE(T4.R, ASCII('B'), 1, 0) 15 + DECODE(T5.R, ASCII('B'), 1, 0) + DECODE(T6.R, ASCII('B'), 1, 0) + DECODE(T7.R, ASCII('B'), 1, 0) 16 + DECODE(T8.R, ASCII('B'), 1, 0) + DECODE(T9.R, ASCII('B'), 1, 0) + DECODE(T10.R, ASCII('B'), 1, 0) B, 17 DECODE(T1.R, 'C', 1, 0) + DECODE(T2.R, ASCII('C'), 1, 0) + DECODE(T3.R, ASCII('C'), 1, 0) + DECODE(T4.R, ASCII('C'), 1, 0) 18 + DECODE(T5.R, ASCII('C'), 1, 0) + DECODE(T6.R, ASCII('C'), 1, 0) + DECODE(T7.R, ASCII('C'), 1, 0) 19 + DECODE(T8.R, ASCII('C'), 1, 0) + DECODE(T9.R, ASCII('C'), 1, 0) + DECODE(T10.R, ASCII('C'), 1, 0) C, 20 DECODE(T1.R, ASCII('D'), 1, 0) + DECODE(T2.R, ASCII('D'), 1, 0) + DECODE(T3.R, ASCII('D'), 1, 0) + DECODE(T4.R, ASCII('D'), 1, 0) 21 + DECODE(T5.R, ASCII('D'), 1, 0) + DECODE(T6.R, ASCII('D'), 1, 0) + DECODE(T7.R, ASCII('D'), 1, 0) 22 + DECODE(T8.R, ASCII('D'), 1, 0) + DECODE(T9.R, ASCII('D'), 1, 0) + DECODE(T10.R, ASCII('D'), 1, 0) D 23 FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9, T T10 24 WHERE DECODE(CHR(T2.R), 'A', 'C', 'B', 'D', 'C', 'A', 'B') = CHR(T5.R) 25 AND (CASE CHR(T3.R) WHEN 'A' THEN LEAST(T2.R, T4.R, T6.R) - GREATEST(T2.R, T4.R, T6.R) 26 WHEN 'B' THEN LEAST(T2.R, T3.R, T4.R) - GREATEST(T2.R, T3.R, T4.R) 27 WHEN 'C' THEN LEAST(T3.R, T4.R, T6.R) - GREATEST(T3.R, T4.R, T6.R) 28 ELSE LEAST(T2.R, T3.R, T6.R) - GREATEST(T2.R, T3.R, T6.R) END) = 0 29 AND (CASE CHR(T3.R) WHEN 'A' THEN T2.R - T3.R 30 WHEN 'B' THEN T2.R - T6.R 31 WHEN 'C' THEN T3.R - T2.R 32 ELSE T2.R - T4.R END) != 0 33 AND (CASE CHR(T4.R) WHEN 'A' THEN T1.R - T5.R 34 WHEN 'B' THEN T2.R - T7.R 35 WHEN 'C' THEN T1.R - T9.R 36 ELSE T6.R - T10.R END) = 0 37 AND (CASE CHR(T5.R) WHEN 'A' THEN T8.R - ASCII('A') 38 WHEN 'B' THEN T4.R - ASCII('B') 39 WHEN 'C' THEN T9.R - ASCII('C') 40 ELSE T10.R - ASCII('D') END) = 0 41 AND (CASE CHR(T6.R) WHEN 'A' THEN LEAST(T2.R, T4.R, T8.R) - GREATEST(T2.R, T4.R, T8.R) 42 WHEN 'B' THEN LEAST(T1.R, T6.R, T8.R) - GREATEST(T1.R, T6.R, T8.R) 43 WHEN 'C' THEN LEAST(T3.R, T10.R, T8.R) - GREATEST(T3.R, T10.R, T8.R) 44 ELSE LEAST(T5.R, T9.R, T8.R) - GREATEST(T5.R, T9.R, T8.R) END) = 0 45 AND (CASE CHR(T8.R) WHEN 'A' THEN ABS(T7.R - T1.R) 46 WHEN 'B' THEN ABS(T5.R - T1.R) 47 WHEN 'C' THEN ABS(T2.R - T1.R) 48 ELSE ABS(T10.R - T1.R) END) != 1 49 AND CASE CHR(T9.R) WHEN 'A' THEN T1.R - T6.R + T5.R - T6.R ELSE 1 END != 0 50 AND CASE CHR(T9.R) WHEN 'A' THEN (T1.R - T6.R) * (T5.R - T6.R) ELSE 0 END = 0 51 AND CASE CHR(T9.R) WHEN 'B' THEN T1.R - T6.R + T5.R - T10.R ELSE 1 END != 0 52 AND CASE CHR(T9.R) WHEN 'B' THEN (T1.R - T6.R) * (T5.R - T10.R) ELSE 0 END = 0 53 AND CASE CHR(T9.R) WHEN 'C' THEN T1.R - T6.R + T5.R - T2.R ELSE 1 END != 0 54 AND CASE CHR(T9.R) WHEN 'C' THEN (T1.R - T6.R) * (T5.R - T2.R) ELSE 0 END = 0 55 AND CASE CHR(T9.R) WHEN 'D' THEN T1.R - T6.R + T5.R - T9.R ELSE 1 END != 0 56 AND CASE CHR(T9.R) WHEN 'D' THEN (T1.R - T6.R) * (T5.R - T9.R) ELSE 0 END = 0) 57 SELECT CHR(R1) R1, CHR(R2) R2, CHR(R3) R3, CHR(R4) R4, CHR(R5) R5, 58 CHR(R6) R6, CHR(R7) R7, CHR(R8) R8, CHR(R9) R9, CHR(R10) R10 59 FROM RESULT 60 WHERE (CASE CHR(R7) WHEN 'A' THEN C 61 WHEN 'B' THEN B 62 WHEN 'C' THEN A 63 ELSE D END) = LEAST(A, B, C, D) 64 AND (CASE CHR(R10) WHEN 'A' THEN 3 65 WHEN 'B' THEN 2 66 WHEN 'C' THEN 4 67 ELSE 1 END) = (GREATEST(A, B, C, D) - LEAST(A, B, C, D)); R1 R2 R3 R4 R5 R6 R7 R8 R9 R10 ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- B C A C A C D A B A

这个SQL的主要逻辑是：

通过 WITH 构造 A、B、C、D 四个选项； 把十道题的所有选项可能性穷举生成； 按照题意通过 WHERE 语句来进行条件限制； 第三题和第九题都是一道题对应一个 WHERE 语句； 第七题和第十题无法在第一层查询中获取，需要在第二层中过滤。

虽然用了一点技巧，但是总的来说SQL的逻辑比较清楚，欢迎大家探讨不同解并投稿给我们。

同类文章回顾：

用SQL解析神奇的扑克牌魔术 用SQL解海盗分金的利益最大化问题 无所不能用SQL挑战经典游戏汉诺塔 SQL竟然可以解脑筋急转弯的题目