SQL刑侦科推理题

跟个风  推一下~

WITH T AS
(
SELECT 'A' AS ID FROM DUAL UNION ALL
SELECT 'B' AS ID FROM DUAL UNION ALL
SELECT 'C' AS ID FROM DUAL UNION ALL
SELECT 'D' AS ID FROM DUAL 
),
X AS(
SELECT 
T1.ID ID1, 
T2.ID ID2,
T3.ID ID3,
T4.ID ID4,
T5.ID ID5,
T6.ID ID6,
T7.ID ID7,
T8.ID ID8,
T9.ID ID9,
T10.ID ID10,
T1.ID||T2.ID||T3.ID||T4.ID||T5.ID||T6.ID||T7.ID||T8.ID||T9.ID||T10.ID ID100
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9,T T10)
SELECT ID100 S FROM X
WHERE ID2=CASE WHEN ID5='C' THEN 'A'
               WHEN ID5='D' THEN 'B'
               WHEN ID5='A' THEN 'C'
               WHEN ID5='B' THEN 'D' END
  AND ID3=CASE WHEN ID3 NOT IN(ID2,ID4,ID6) THEN 'A'
               WHEN ID6 NOT IN(ID3,ID4,ID2) THEN 'B'
               WHEN ID2 NOT IN(ID3,ID4,ID6) THEN 'C' 
               WHEN ID4 NOT IN(ID3,ID6,ID2) THEN 'D' END
  AND ID4=CASE WHEN ID1=ID5 THEN 'A' 
               WHEN ID2=ID7 THEN 'B'
               WHEN ID1=ID9 THEN 'C'
               WHEN ID6=ID10 THEN 'D' END
  AND ID5=CASE WHEN INSTR(ID8||ID4||ID9||ID7,ID5)=1 THEN 'A'
               WHEN INSTR(ID8||ID4||ID9||ID7,ID5)=2 THEN 'B'
               WHEN INSTR(ID8||ID4||ID9||ID7,ID5)=3 THEN 'C'
               WHEN INSTR(ID8||ID4||ID9||ID7,ID5)=4 THEN 'D' END
  AND ID6=CASE WHEN ID8||ID8=ID2||ID4  THEN 'A'
               WHEN ID8||ID8=ID1||ID6  THEN 'B'
               WHEN ID8||ID8=ID3||ID10 THEN 'C'
               WHEN ID8||ID8=ID5||ID9  THEN 'D' END
  AND ID7=DECODE(   
      GREATEST(LENGTH(REPLACE(ID100,'A','')),
               LENGTH(REPLACE(ID100,'B','')),
               LENGTH(REPLACE(ID100,'C','')),
               LENGTH(REPLACE(ID100,'D',''))),
               LENGTH(REPLACE(ID100,'C','')) , 'A',
               LENGTH(REPLACE(ID100,'B','')) , 'B',
               LENGTH(REPLACE(ID100,'A','')) , 'C',
               LENGTH(REPLACE(ID100,'D','')) , 'D' )
     AND ID8=CASE WHEN ID1||ID7  NOT IN('AB','BA','BC','CB','CD','DC') THEN 'A'
                  WHEN ID1||ID5  NOT IN('AB','BA','BC','CB','CD','DC') THEN 'B'
                  WHEN ID1||ID2  NOT IN('AB','BA','BC','CB','CD','DC') THEN 'C'
                  WHEN ID1||ID10 NOT IN('AB','BA','BC','CB','CD','DC') THEN 'D' END
     AND ID9=CASE WHEN DECODE(ID1,ID6,1,0)+DECODE(ID6 ,ID5,1,0)=1 THEN 'A'
                  WHEN DECODE(ID1,ID6,1,0)+DECODE(ID10,ID5,1,0)=1 THEN 'B'
                  WHEN DECODE(ID1,ID6,1,0)+DECODE(ID2 ,ID5,1,0)=1 THEN 'C'
                  WHEN DECODE(ID1,ID6,1,0)+DECODE(ID9 ,ID5,1,0)=1 THEN 'D' END
     AND ID10=DECODE(GREATEST(LENGTH(REPLACE(ID100,'A','')),
               LENGTH(REPLACE(ID100,'B','')),
               LENGTH(REPLACE(ID100,'C','')),
               LENGTH(REPLACE(ID100,'D','')))
               -LEAST(LENGTH(REPLACE(ID100,'A','')),
               LENGTH(REPLACE(ID100,'B','')),
               LENGTH(REPLACE(ID100,'C','')),
               LENGTH(REPLACE(ID100,'D',''))),3,'A',2,'B',4,'C',1,'D')

答案是---------BCACACDABA-----------

思路很简单，笛卡尔积出所有的结果（4的10次方行数据），然后使用题中的已知内容构造where条件