分享一波SQL刑侦科推理题-SQLServe

SQL Server 21NN.CN 5个月前 (05-24) 51次浏览 已收录 0个评论 扫描二维码

跟个风 推一下~

分享一波SQL刑侦科推理题-SQLServe

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条件


本文:分享一波SQL刑侦科推理题-SQLServe 本文链接:https://www.21nn.cn/shujuku/sqlserver/36954.html 本站所以图片、文章仅限用于学习和研究目的; 不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。 本站信息来自网络,版权争议与本站无关。 您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 如有侵权请邮件与我们联系处理。i@ki4.cn
喜欢 (0)
[1353713598@qq.com]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址