高手过招:用SQL解决环环相扣的刑侦推理问题(罗海雄版本)
本文是继 杨长老 刑侦高考：如何用SQL解决环环相扣的刑侦推理问题 之后（点击阅读原文查看历史信息），罗海雄老师提供了一个带注释的版本，希望初学者也能够直接看懂，不仅仅是Oracle的版本，同时MySQL的版本也来了。
试图如图：
思路如下：
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 的也来了，大家品鉴！
资源下载
关注公众号：数据和云（OraNews）回复关键字获取
2018DTCC , 数据库大会PPT
2017DTC，2017 DTC 大会 PPT
DBALIFE ，“DBA 的一天”海报
DBA04 ，DBA 手记4 电子书
122ARCH ，Oracle 12.2体系结构图
2017OOW ，Oracle OpenWorld 资料
PRELECTION ，大讲堂讲师课程资料