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

输出效果

使用分表方式

    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(+)