# 分享一波SQL刑侦科推理题

18-07-28

```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')```