一个sql语句如下:
select a.*, b.sryxm,
(case when g.slllb='1' then g.sryxm||' (A信息库)'
when g.slllb='2' then g.sryxm||' (B信息库)'
when g.slllb='3' then g.sryxm||' (C信息库)'
when i.sryxm is not null then i.sryxm||' (D信息库)'
when p.xm is not null then p.xm||'(E信息库)'
when m.xm is not null then m.xm||'(F信息库)'
when n.xm is not null then n.xm||'(G信息库)'
when t.syr is not null then t.syr||'(H信息库)'
end ) sinfo
from ( z_tellistmx a
left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
left join z_address g on a.sdfhm=g.sdhhm
left join z_teluser h on g.nuser_sn=h.nuser_sn
left join z_teluser i on a.sdfhm=i.sdhhm
left join syn_czzzrk p on a.sdfhm=p.lxdh
left join syn_czczrk m on a.sdfhm=m.lxdh
left join syn_czjsyxx n on a.sdfhm=n.lxdh
left join syn_czjdcxx t on a.sdfhm=t.lxdh )
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053' order by a.dhjrq asc执行sql语句的结果本应该如下:
1 15351967653 61 潘琪 小小 (A信息库)
2 15351967653 61 潘琪 张凌虹 (C信息库)
3 15351967653 61 潘琪 房晓华(F信息库)
4 15351967653 61 潘琪 王夷(F信息库)
5 15351967653 61 潘琪 黄苏林(G信息库)
6 15351967653 61 潘琪 刘华(G信息库) 结果得到了如下结果:
1 15351967653 61 潘琪 小小 (A信息库)
2 15351967653 61 潘琪 小小 (A信息库)
3 15351967653 61 潘琪 小小 (A信息库)
4 15351967653 61 潘琪 张凌虹 (C信息库)
5 15351967653 61 潘琪 张凌虹 (C信息库)
6 15351967653 61 潘琪 小小 (A信息库)
7 15351967653 61 潘琪 张凌虹 (C信息库)
8 15351967653 61 潘琪 张凌虹 (C信息库)
请问这条sql语句该如何改写啊,谢谢
select a.*, b.sryxm,
(case when g.slllb='1' then g.sryxm||' (A信息库)'
when g.slllb='2' then g.sryxm||' (B信息库)'
when g.slllb='3' then g.sryxm||' (C信息库)'
when i.sryxm is not null then i.sryxm||' (D信息库)'
when p.xm is not null then p.xm||'(E信息库)'
when m.xm is not null then m.xm||'(F信息库)'
when n.xm is not null then n.xm||'(G信息库)'
when t.syr is not null then t.syr||'(H信息库)'
end ) sinfo
from ( z_tellistmx a
left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
left join z_address g on a.sdfhm=g.sdhhm
left join z_teluser h on g.nuser_sn=h.nuser_sn
left join z_teluser i on a.sdfhm=i.sdhhm
left join syn_czzzrk p on a.sdfhm=p.lxdh
left join syn_czczrk m on a.sdfhm=m.lxdh
left join syn_czjsyxx n on a.sdfhm=n.lxdh
left join syn_czjdcxx t on a.sdfhm=t.lxdh )
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053' order by a.dhjrq asc执行sql语句的结果本应该如下:
1 15351967653 61 潘琪 小小 (A信息库)
2 15351967653 61 潘琪 张凌虹 (C信息库)
3 15351967653 61 潘琪 房晓华(F信息库)
4 15351967653 61 潘琪 王夷(F信息库)
5 15351967653 61 潘琪 黄苏林(G信息库)
6 15351967653 61 潘琪 刘华(G信息库) 结果得到了如下结果:
1 15351967653 61 潘琪 小小 (A信息库)
2 15351967653 61 潘琪 小小 (A信息库)
3 15351967653 61 潘琪 小小 (A信息库)
4 15351967653 61 潘琪 张凌虹 (C信息库)
5 15351967653 61 潘琪 张凌虹 (C信息库)
6 15351967653 61 潘琪 小小 (A信息库)
7 15351967653 61 潘琪 张凌虹 (C信息库)
8 15351967653 61 潘琪 张凌虹 (C信息库)
请问这条sql语句该如何改写啊,谢谢
前面的编号不用管它,我是从PLSQL Developer工具中拷贝出来的,是工具中的列表序号
(case when g.slllb='1' then g.sryxm||' (A信息库)'
when g.slllb='2' then g.sryxm||' (B信息库)'
when g.slllb='3' then g.sryxm||' (C信息库)'
end ) sinfo
from ( z_tellistmx a
inner join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join z_address g on a.sdfhm=g.sdhhm
)
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053'
union all
select a.*,b.sryxm,i.sryxm||' (D信息库)'
from ( z_tellistmx a
inner join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join z_teluser i on a.sdfhm=i.sdhhm)
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053' and i.sryxm is not null
union all
select a.*,b.sryxm,p.xm||'(E信息库)'
from ( z_tellistmx a
inner join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czzzrk p on a.sdfhm=p.lxdh)
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053' and p.xm is not null
union all
select a.*,b.sryxm,m.xm||'(F信息库)'
from ( z_tellistmx a
inner join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czczrk m on a.sdfhm=m.lxdh)
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053' and m.xm is not null
union all
select a.*,b.sryxm,n.xm||'(G信息库)'
from ( z_tellistmx a
inner join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czjsyxx n on a.sdfhm=n.lxdh)
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053' and n.xm is not null
union all
select a.*,b.sryxm,t.syr||'(H信息库)'
from ( z_tellistmx a
inner join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czjdcxx t on a.sdfhm=t.lxdh)
where 1=1 and a.sdhhm in (13861265182) and b.sajbh='050643101011160053' and t.syr is not null