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

 

主要是通过WITH构造ABCD四个选项,然后把十道题的所有选项可能性穷举生成,然后按照题意通过WHERE语句来进行条件限制,除了第三题和第九题都是一道题对应一个WHERE 语句,另外第七题和第十题无法在第一层查询中获取,需要在第二层中过滤。

虽然用了一点技巧,但是总的来说没啥技术含量,这里就不逐一解释了。

This entry was posted in ORACLE and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *