高手過招:用SQL解決環環相扣的刑偵推理問題(羅海雄版本)

試圖如圖：

思路如下：

1. 構造帶 A/B/C/D 四個答案的題目。

2. 把除了第 7 和第 10 題的之外的題目分別用表示式寫出來。

3. 由於第 7 第 10 題涉及到所有答案的綜合資訊，所以外面再套一層，計算 10 道題的每個答案出現的次數（通過 REPLACE 掉特定答案後字串長度變化來計算特定答案的總個數）作為輔助列，然後再繼續判斷。

WITH Q AS (/*構造A/B/C/D四個選項 */

SELECT `A` AS A FROM DUAL

UNION ALL SELECT `B` FROM DUAL

UNION ALL SELECT `C` FROM DUAL

UNION ALL SELECT `D` FROM DUAL)

SELECT * FROM (

SELECT TMP.*

/* 用REPLACE掉特定答案後字串長度變化來計算特定答案的總個數*/

,10-LENGTH(REPLACE(A_ALL,`A`)) A_CNT

,10-LENGTH(REPLACE(A_ALL,`B`)) B_CNT

,10-LENGTH(REPLACE(A_ALL,`C`)) C_CNT

,10-LENGTH(REPLACE(A_ALL,`D`)) D_CNT

FROM ( /* 構造10個題目，其中第10題用Q0指代 */

SELECT Q1.A A1, Q2.A A2, Q3.A A3, Q4.A A4, Q5.A A5,

Q6.A A6, Q7.A A7, Q8.A A8, Q9.A A9, Q0.A A0,

Q1.A||Q2.A||Q3.A||Q4.A||Q5.A||Q6.A

||Q7.A||Q8.A||Q9.A||Q0.A A_ALL

FROM Q Q1,Q Q2,Q Q3,Q Q4,Q Q5,Q Q6,Q Q7,Q Q8,Q Q9,Q Q0

WHERE /* 題目1恆等*/ 1=1

AND (/*題目2*/ Q2.A=`A` AND Q5.A=`C`

OR Q2.A=`B` AND Q5.A=`D`

OR Q2.A=`C` AND Q5.A=`A`

OR Q2.A=`D` AND Q5.A=`B`)

AND (/*題目3*/ Q3.A=`A` AND Q3.A NOT IN (Q2.A,Q4.A,Q6.A)

AND Q2.A=Q4.A AND Q2.A=Q6.A

OR Q3.A=`B` AND Q6.A NOT IN (Q2.A,Q3.A,Q4.A)

AND Q2.A=Q3.A AND Q2.A=Q4.A

OR Q3.A=`C` AND Q2.A NOT IN (Q3.A,Q4.A,Q6.A)

AND Q3.A=Q4.A AND Q3.A=Q6.A

OR Q3.A=`D` AND Q4.A NOT IN (Q2.A,Q3.A,Q6.A)

AND Q2.A=Q3.A AND Q2.A=Q6.A)

AND (/*題目4*/ Q4.A=`A` AND Q1.A=Q5.A

OR Q4.A=`B` AND Q2.A=Q7.A

OR Q4.A=`C` AND Q1.A=Q9.A

OR Q4.A=`D` AND Q2.A=Q0.A)

AND (/*題目5*/ Q5.A=`A` AND Q5.A=Q8.A

OR Q5.A=`B` AND Q5.A=Q4.A

OR Q5.A=`C` AND Q5.A=Q9.A

OR Q5.A=`D` AND Q5.A=Q7.A)

AND (/*題目6*/ Q6.A=`A` AND Q8.A=Q2.A AND Q8.A=Q4.A

OR Q6.A=`B` AND Q8.A=Q1.A AND Q8.A=Q6.A

OR Q6.A=`C` AND Q8.A=Q3.A AND Q8.A=Q0.A

OR Q6.A=`D` AND Q8.A=Q5.A AND Q8.A=Q9.A)

AND (/*題目8*/ Q8.A=`A` AND ABS(ASCII(Q1.A)-ASCII(Q7.A))!=1

/*不相鄰就是ASCII碼相差不為1或-1*/

OR Q8.A=`B` AND ABS(ASCII(Q1.A)-ASCII(Q5.A))!=1

OR Q8.A=`C` AND ABS(ASCII(Q1.A)-ASCII(Q2.A))!=1

OR Q8.A=`D` AND ABS(ASCII(Q1.A)-ASCII(Q0.A))!=1)

AND (/*題目9*/ Q9.A=`A` AND ((Q1.A=Q6.A AND Q5.A!=Q6.A)

OR (Q1.A!=Q6.A AND Q5.A=Q6.A))

OR Q9.A=`B` AND ((Q1.A=Q6.A AND Q5.A!=Q0.A)

OR (Q1.A!=Q6.A AND Q5.A=Q0.A))

OR Q9.A=`C` AND ((Q1.A=Q6.A AND Q5.A!=Q2.A)

OR (Q1.A!=Q6.A AND Q5.A=Q2.A))

OR Q9.A=`D` AND ((Q1.A=Q6.A AND Q5.A!=Q9.A)

OR (Q1.A!=Q6.A AND Q5.A=Q9.A)))

) TMP)

WHERE (/*題目7*/ A7=`A` AND C_CNT < LEAST(A_CNT,B_CNT,D_CNT)

OR A7=`B` AND B_CNT < LEAST(A_CNT,C_CNT,D_CNT)

OR A7=`C` AND A_CNT < LEAST(B_CNT,C_CNT,D_CNT)

OR A7=`D` AND D_CNT < LEAST(A_CNT,B_CNT,C_CNT))

AND (/*題目10*/ A0=`A` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 3

OR A0=`B` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 2

OR A0=`C` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 4

OR A0=`D` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 1)

另外，不僅僅是Oracle可以做到，MySQL也可以做到，在上面SQL的基礎上稍作改動，就可以在MySQL中也輕鬆得到答案。

CREATE TABLE Q AS
SELECT ‘A’ union SELECT ‘B’ union SELECT ‘C’ union SELECT ‘D’;

SELECT * FROM (

SELECT TMP.*

/* 用REPLACE掉特定答案後字串長度變化來計算特定答案的總個數*/

,10-LENGTH(REPLACE(A_ALL,`A`,``)) A_CNT

,10-LENGTH(REPLACE(A_ALL,`B`,``)) B_CNT

,10-LENGTH(REPLACE(A_ALL,`C`,``)) C_CNT

,10-LENGTH(REPLACE(A_ALL,`D`,``)) D_CNT

FROM ( /* 構造10個題目，其中第10題用Q0指代 */

SELECT Q1.A A1, Q2.A A2, Q3.A A3, Q4.A A4, Q5.A A5,

Q6.A A6, Q7.A A7, Q8.A A8, Q9.A A9, Q0.A A0,

CONCAT(Q1.A,Q2.A,Q3.A,Q4.A,Q5.A,Q6.A

,Q7.A,Q8.A,Q9.A,Q0.A) A_ALL

FROM Q Q1,Q Q2,Q Q3,Q Q4,Q Q5,Q Q6,Q Q7,Q Q8,Q Q9,Q Q0

WHERE /* 題目1恆等*/ 1=1

AND (/*題目2*/ Q2.A=`A` AND Q5.A=`C`

OR Q2.A=`B` AND Q5.A=`D`

OR Q2.A=`C` AND Q5.A=`A`

OR Q2.A=`D` AND Q5.A=`B`)

AND (/*題目3*/ Q3.A=`A` AND Q3.A NOT IN (Q2.A,Q4.A,Q6.A)

AND Q2.A=Q4.A AND Q2.A=Q6.A

OR Q3.A=`B` AND Q6.A NOT IN (Q2.A,Q3.A,Q4.A)

AND Q2.A=Q3.A AND Q2.A=Q4.A

OR Q3.A=`C` AND Q2.A NOT IN (Q3.A,Q4.A,Q6.A)

AND Q3.A=Q4.A AND Q3.A=Q6.A

OR Q3.A=`D` AND Q4.A NOT IN (Q2.A,Q3.A,Q6.A)

AND Q2.A=Q3.A AND Q2.A=Q6.A)

AND (/*題目4*/ Q4.A=`A` AND Q1.A=Q5.A

OR Q4.A=`B` AND Q2.A=Q7.A

OR Q4.A=`C` AND Q1.A=Q9.A

OR Q4.A=`D` AND Q2.A=Q0.A)

AND (/*題目5*/ Q5.A=`A` AND Q5.A=Q8.A

OR Q5.A=`B` AND Q5.A=Q4.A

OR Q5.A=`C` AND Q5.A=Q9.A

OR Q5.A=`D` AND Q5.A=Q7.A)

AND (/*題目6*/ Q6.A=`A` AND Q8.A=Q2.A AND Q8.A=Q4.A

OR Q6.A=`B` AND Q8.A=Q1.A AND Q8.A=Q6.A

OR Q6.A=`C` AND Q8.A=Q3.A AND Q8.A=Q0.A

OR Q6.A=`D` AND Q8.A=Q5.A AND Q8.A=Q9.A)

AND (/*題目8*/ Q8.A=`A` AND ABS(ASCII(Q1.A)-ASCII(Q7.A))!=1

/*不相鄰就是ASC碼相差不為1或-1*/

OR Q8.A=`B` AND ABS(ASCII(Q1.A)-ASCII(Q5.A))!=1

OR Q8.A=`C` AND ABS(ASCII(Q1.A)-ASCII(Q2.A))!=1

OR Q8.A=`D` AND ABS(ASCII(Q1.A)-ASCII(Q0.A))!=1)

AND (/*題目9*/ Q9.A=`A` AND ((Q1.A=Q6.A AND Q5.A!=Q6.A)

OR (Q1.A!=Q6.A AND Q5.A=Q6.A))

OR Q9.A=`B` AND ((Q1.A=Q6.A AND Q5.A!=Q0.A)

OR (Q1.A!=Q6.A AND Q5.A=Q0.A))

OR Q9.A=`C` AND ((Q1.A=Q6.A AND Q5.A!=Q2.A)

OR (Q1.A!=Q6.A AND Q5.A=Q2.A))

OR Q9.A=`D` AND ((Q1.A=Q6.A AND Q5.A!=Q9.A)

OR (Q1.A!=Q6.A AND Q5.A=Q9.A)))

) TMP)TMP2

WHERE (/*題目7*/ A7=`A` AND C_CNT < LEAST(A_CNT,B_CNT,D_CNT)

OR A7=`B` AND B_CNT < LEAST(A_CNT,C_CNT,D_CNT)

OR A7=`C` AND A_CNT < LEAST(B_CNT,C_CNT,D_CNT)

OR A7=`D` AND D_CNT < LEAST(A_CNT,B_CNT,C_CNT))

AND (/*題目10*/ A0=`A` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 3

OR A0=`B` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 2

OR A0=`C` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 4

OR A0=`D` AND GREATEST(A_CNT,B_CNT,C_CNT,D_CNT)

- LEAST(A_CNT,B_CNT,C_CNT,D_CNT) = 1)

這一次不僅僅是 Oracle 的版本，MySQL 的也來了，大家品鑑！

原文釋出時間為：2018-06-14

本文作者：羅海雄

本文來自雲棲社群合作伙伴“資料和雲”，瞭解相關資訊可以關注“資料和雲”。