高手过招:用SQL解决环环相扣的刑侦推理问题(罗海雄版本)

罗海雄 2018-06-14
477

关注我们获得更多精彩内容!


本文是继 杨长老 刑侦高考:如何用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 ,大讲堂讲师课程资料

近期文章

仅仅使用AWR做报告? 性能优化还未入门

实战课堂:一则CPU 100%的故障分析

杨廷琨:如何编写高效SQL(含PPT)

一份高达555页的技术PPT会是什么样子?

大象起舞:用PostgreSQL解海盗分金问题

最后修改时间:2021-01-29 11:31:27
文章转载自罗海雄,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论