高手过招:用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)

原文发表时间:2018-06-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏深度学习之tensorflow实战篇

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)/ ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY CO

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法      今天在使用多字段去重时,由于某些字段有多种可...

2643
来自专栏菩提树下的杨过

无限级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(1)表结构

无限分类是一个老生常谈的话题了,网上有很多解决方案,可以分成二个流派,一种利用递归,一种利用非递归(当然需要其它一些辅助手段判断节点层次),但核心表结构都差不多...

2206
来自专栏钟绍威的专栏

当子查询碰上NULLUNIONJOIN总结

情景: 现在有如图两个表,boy和girl,对应着Boy和Girl两个bean,有共同字段id、name,另外boy还有个外键grilfriend(指向girl...

1847
来自专栏PHP在线

mysql

cross join :笛卡尔交集。 Inner join :内连接。 left join :只要满足左边表的需求就可以了,右表有无都可以。 right ...

3698
来自专栏一个爱吃西瓜的程序员

学习SQL【8】-谓词和CASE表达式

谓词 什么是谓词 谓词就是返回值为真值的函数。对于通常的函数来说,返回值有可能是数字、字符串和日期等,但是谓词的返回值全部是真值。这也是谓词和函数的最大区别。 ...

3356
来自专栏数据之美

图文并茂详解 SQL JOIN

Join是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另...

2778
来自专栏程序员宝库

MYSQL 业务上碰到的 SQL 问题整理集合

前言 身为一名前端工程师,对于 SQL了解程度并不是很深刻,盘点一些个人工作遇到的问题,给大家普及下知识,以及记录自己如何解决这些问题的。 导航 SELECT ...

3436
来自专栏面朝大海春暖花开

mybatis递归,一对多代码示例

由于只有这么两级,可以不用使用递归,直接查询父集,之后foreach查询子集放入对应的list集合。

1931
来自专栏算法修养

LeetCode 116 Populating Next Right Pointers in Each Node

Populate each next pointer to point to its next right node. If there is no next ...

822
来自专栏乐沙弥的世界

批量 SQL 之 FORALL 语句

    对PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL引擎...

892

扫码关注云+社区

领取腾讯云代金券