输出效果
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/fd9fa587de1629827aaee41def6b645e.png)
使用分表方式
select MM.IMSI,MM.MSISDN,
CASE SS.CFU
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(TCFU.FTN,'定制')
ELSE '未知' END CFU,
CASE SS.CFB
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(TCFB.FTN,'定制')
ELSE '未知' END CFB,
CASE SS.CFNRY
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(TCFNRY.FTN,'定制')
ELSE '未知' END CFNRY,
CASE SS.CFNRC
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(TCFNRC.FTN,'定制')
ELSE '未知' END CFNRC,
CASE SS.CFD_CFB
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(TCFD.FTN,'覆盖')
ELSE '未知' END CFD_CFB,
CASE SS.CFD_CFNRY
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(TCFD.FTN,'覆盖')
ELSE '未知' END CFD_CFNRY,
CASE SS.CFD_CFNRC
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
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(ssa.cfu,'定制')
ELSE '未知' END CFU,
CASE SS.CFB
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(ssa.cfb,'定制')
ELSE '未知' END CFB,
CASE SS.CFNRY
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(ssa.cfnry,'定制')
ELSE '未知' END CFNRY,
CASE SS.CFNRC
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(ssa.cfnrc,'定制')
ELSE '未知' END CFNRC,
CASE SS.CFD_CFB
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(ssa.CFD,'覆盖')
END CFD_CFB,
CASE SS.CFD_CFNRY
WHEN 0 THEN 'X'
WHEN 1 THEN nvl(ssa.CFD,'覆盖')
END CFD_CFNRY,
CASE SS.CFD_CFNRC
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(+)