每个用户一行展示呼叫转移状态SQL语句（HWHB）

本文链接：https://blog.youkuaiyun.com/ddrfan/article/details/85262757

本文详细介绍了使用SQL进行复杂查询的方法，包括使用CASE语句进行条件判断，通过子查询和联表查询获取多个表中的数据，以及利用DECODE函数简化查询逻辑。案例展示了如何在一个查询中整合多个表的数据，实现对特定条件的有效筛选。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

输出效果
使用分表方式
使用decode函数

输出效果

在这里插入图片描述

使用分表方式

    select MM.IMSI,MM.MSISDN,
    CASE SS.CFU --开始判定CFU 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(TCFU.FTN,'定制')
    ELSE '未知' END CFU,
    CASE SS.CFB --开始判定CFB  
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(TCFB.FTN,'定制')
    ELSE '未知' END CFB,
    CASE SS.CFNRY --开始判定CFNRY 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(TCFNRY.FTN,'定制')
    ELSE '未知' END CFNRY,
    CASE SS.CFNRC --开始判定CFNRC 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(TCFNRC.FTN,'定制')
    ELSE '未知' END CFNRC,
    CASE SS.CFD_CFB --开始判定CFDCFB 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(TCFD.FTN,'覆盖')
    ELSE '未知' END CFD_CFB,
    CASE SS.CFD_CFNRY --开始判定CFDCFNRY 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(TCFD.FTN,'覆盖')
    ELSE '未知' END CFD_CFNRY,
    CASE SS.CFD_CFNRC --开始判定CFDCFB 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(TCFD.FTN,'覆盖') 
    ELSE '未知' END CFD_CFNRC
    from XHWHB_2042_MAIN mm,XHWHB_2042_SS ss,
    (select IMSI,FTN from XHWHB_2042_SS_ACT a1 where A1.BSG='TS10' and A1.SS='CFU' and A1.ACT=1) TCFU,
    (select IMSI,FTN from XHWHB_2042_SS_ACT a1 where A1.BSG='TS10' and A1.SS='CFB' and A1.ACT=1) TCFB,
    (select IMSI,FTN from XHWHB_2042_SS_ACT a1 where A1.BSG='TS10' and A1.SS='CFNRY' and A1.ACT=1) TCFNRY,
    (select IMSI,FTN from XHWHB_2042_SS_ACT a1 where A1.BSG='TS10' and A1.SS='CFNRC' and A1.ACT=1) TCFNRC,
    (select IMSI,FTN,ACT from XHWHB_2042_SS_ACT a1 where A1.BSG='TS10' and A1.SS='CFD' and A1.ACT=1) TCFD
    where MM.IMSI=SS.IMSI 
    and MM.IMSI=TCFU.IMSI(+)
    and MM.IMSI=TCFB.IMSI(+)
    and MM.IMSI=TCFNRY.IMSI(+)
    and MM.IMSI=TCFNRC.IMSI(+)
    and MM.IMSI=TCFD.IMSI(+)

使用decode函数

    select MM.IMSI,MM.MSISDN,
    CASE SS.CFU --开始判定CFU 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(ssa.cfu,'定制')
    ELSE '未知' END CFU,
    CASE SS.CFB --开始判定CFB  
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(ssa.cfb,'定制') 
    ELSE '未知' END CFB,
    CASE SS.CFNRY --开始判定CFNRY 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(ssa.cfnry,'定制') 
    ELSE '未知' END CFNRY,
    CASE SS.CFNRC --开始判定CFNRC 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(ssa.cfnrc,'定制')
    ELSE '未知' END CFNRC,
    CASE SS.CFD_CFB --开始判定CFDCFB 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(ssa.CFD,'覆盖')
    END CFD_CFB,
    CASE SS.CFD_CFNRY --开始判定CFDCFNRY 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(ssa.CFD,'覆盖')
    END CFD_CFNRY,
    CASE SS.CFD_CFNRC --开始判定CFDCFB 
    WHEN 0 THEN 'X'  
    WHEN 1 THEN nvl(ssa.CFD,'覆盖')
    END CFD_CFNRC
    from XHWHB_2042_MAIN mm,XHWHB_2042_SS ss,
    (select sa.IMSI,
    Max(decode(sa.SS, 'CFU',sa.ftn,null)) CFU, 
    Max(decode(sa.SS, 'CFB',sa.ftn,null)) CFB,
    Max(decode(sa.SS, 'CFNRY',sa.ftn,null)) CFNRY,
    Max(decode(sa.SS, 'CFNRC',sa.ftn,null)) CFNRC,
    Max(decode(sa.SS, 'CFD',sa.ftn,null)) CFD
    from XHWHB_2042_SS_ACT sa
    where sa.ACT=1 and sa.bsg='TS10' and sa.ss in('CFU','CFB','CFNRY','CFNRC','CFD')
    group by sa.imsi
    ) ssa
    where MM.IMSI=SS.IMSI 
    and MM.IMSI=ssa.IMSI(+)