高手过招:用SQL解决环环相扣的刑侦推理问题(苏旭辉版本)

高手过招:用SQL解决环环相扣的刑侦推理问题(苏旭辉版本)

苏旭晖，网名 newkid，ITPUB 开发版资深版主，SQL开发专家

本文是继 杨长老 刑侦高考：如何用SQL解决环环相扣的刑侦推理问题  之后，苏旭辉的一个版本，希望大家能够在高手的过招中，看到喜爱、坚持、执着与技艺。

试题再次如图：

 

 

第一个解法如下：

with q as 

(select 'A' c FROM DUAL 

UNION ALL 

SELECT 'B' FROM DUAL 

UNION ALL 

SELECT 'C' FROM DUAL 

UNION ALL SELECT 'D' FROM DUAL)

,q2 as (

SELECT q1.c||q2.c||q3.c||q4.c||q5.c||q6.c||q7.c||q8.c||q9.c||q10.c s

      ,q7.c c7

      ,q10.c c10

  FROM q q1,q q2,q q3,q q4,q q5,q q6,q q7,q q8,q q9,q q10

WHERE q2.c||q5.c IN ('AC','BD','CA','DB')

       AND  q3.c = CASE WHEN q3.c NOT IN (q6.c,q2.c,q4.c) THEN 'A'

                        WHEN q6.c NOT IN (q3.c,q2.c,q4.c) THEN 'B'

                        WHEN q2.c NOT IN (q3.c,q6.c,q4.c) THEN 'C'

                        WHEN q4.c NOT IN (q3.c,q6.c,q2.c) THEN 'D'

                   END

       AND q4.c=CASE WHEN q1.c=q5.c THEN 'A'

                    WHEN q2.c=q7.c THEN 'B'

                    WHEN q1.c=q9.c THEN 'C'

                    WHEN q6.c=q10.c THEN 'D'

               END

       AND q5.c=DECODE(q5.c,q8.c,'A',q4.c,'B',q9.c,'C',q7.c,'D')

       AND q6.c=DECODE(q8.c||q8.c,q2.c||q4.c,'A',q1.c||q6.c,'B',q3.c||q10.c,'C',q5.c||q9.c,'D')

       AND ABS(DECODE(q8.c,'A',ASCII(q7.c)

                      ,'B',ASCII(q5.c)

                      ,'C',ASCII(q2.c)

                      ,'D',ASCII(q10.c)

                      )-ASCII(q1.c))<>1

       AND DECODE(q1.c,q6.c,1,-1) =- DECODE(DECODE(q9.c,'A',q6.c,'B',q10.c,'C',q2.c,'D',q9.c),q5.c,1,-1)

)

,q3 AS (SELECT q2.*,REGEXP_COUNT(s,'A') ca,REGEXP_COUNT(s,'B') cb,REGEXP_COUNT(s,'C') cc,REGEXP_COUNT(s,'D') cd FROM q2)

SELECT s

  FROM q3

WHERE c7=DECODE(LEAST(ca,cb,cc,cd),cc,'A',cb,'B',ca,'C',cd,'D')

       AND c10=DECODE(GREATEST(ca,cb,cc,cd)-LEAST(ca,cb,cc,cd),3,'A',2,'B',4,'C',1,'D')

;

 

S

----------

BCACACDABA

 

如果用1234取代ABCD，最后再变回来，那么中间有些逻辑判断可以用算术，代码就更简单：

 

with q as 

(select LEVEL c FROM DUAL CONNECT BY LEVEL<=4)

,q2 as (

SELECT q1.c||q2.c||q3.c||q4.c||q5.c||q6.c||q7.c||q8.c||q9.c||q10.c s

      ,q7.c c7

      ,q10.c c10

  FROM q q1,q q2,q q3,q q4,q q5,q q6,q q7,q q8,q q9,q q10

WHERE q2.c||q5.c IN ('13','24','31','42')

       AND  q3.c = CASE WHEN q3.c NOT IN (q6.c,q2.c,q4.c) THEN 1

                        WHEN q6.c NOT IN (q3.c,q2.c,q4.c) THEN 2

                        WHEN q2.c NOT IN (q3.c,q6.c,q4.c) THEN 3

                        WHEN q4.c NOT IN (q3.c,q6.c,q2.c) THEN 4

                   END

       AND DECODE(q4.c,1,q1.c-q5.c,2,q2.c-q7.c,3,q1.c-q9.c,4,q6.c-q10.c)=0

       AND q5.c=DECODE(q5.c,q8.c,1,q4.c,2,q9.c,3,q7.c,4)

       AND q6.c=DECODE(q8.c||q8.c,q2.c||q4.c,1,q1.c||q6.c,2,q3.c||q10.c,3,q5.c||q9.c,4)

       AND ABS(DECODE(q8.c,1,q7.c,2,q5.c,3,q2.c,4,q10.c)-q1.c)<>1

       AND DECODE(q1.c,q6.c,1,-1) =- DECODE(DECODE(q9.c,1,q6.c,2,q10.c,3,q2.c,4,q9.c),q5.c,1,-1)

)

,q3 AS (SELECT q2.*,REGEXP_COUNT(s,'1') ca,REGEXP_COUNT(s,'2') cb,REGEXP_COUNT(s,'3') cc,REGEXP_COUNT(s,'4') cd FROM q2)

SELECT TRANSLATE(s,'1234','ABCD')

  FROM q3

WHERE c7=DECODE(LEAST(ca,cb,cc,cd),cc,1,cb,2,ca,3,cd,4)

       AND c10=DECODE(GREATEST(ca,cb,cc,cd)-LEAST(ca,cb,cc,cd),3,1,2,2,4,3,1,4)

;

 

TRANSLATE(S,'1234','ABCD')

-------------------------------------

BCACACDABA

 

SQL 的世界，精彩一如既往。