with queryTable as
(select ta.zhubm as 户编码,
ta.zc,
ta.frenkid,
ta.mrenkid,
ta.zrenkid,
ta.fx as 男姓名,
ta.fj as 男方居住地,
ta.fh as 男方户籍地,
ta.mx as 女方姓名,
ta.mc as 女方出生年月,
ta.mj as 女方居住地,
ta.mh as 女方户籍地,
-- a.mgl as 女方管理属性变动时间,
ta.xynhsh as 现有男孩数,
ta.xynvhsh as 现有女孩数,
ta.zx as 姓名,
ta.xingb as 性别,
ta.zcnw as 政策内外,
ta.zc as 出生年月,
ta.zj as 现居住地,
ta.zh as 户籍所在地,
ta.mcszl as 措施类型,
ta.mcssj as 措施起始时间,
--a.mwcyy as 未采取措施原因,
'' as 备注
from (select row_number() OVER(PARTITION BY taz.sysid ORDER BY tam.sysid, taf.sysid) as rn,
nvl(taz.hubm, tam.hubm) as zhubm,
taf.xingm as fx,
decode(taf.juzhd, tagl.juzhd, tat.climename, tafj.xianxc) as fj,
decode(taf.hujd, tagl.juzhd, tat.climename, tafh.xianxc) as fh,
tam.xingm as mx,
tam.chushrq as mc,
decode(tam.juzhd, tagl.juzhd, tat.climename, tamj.xianxc) as mj,
decode(tam.hujd, tagl.juzhd, tat.climename, tamh.xianxc) as mh,
tamgl.guanlshxbdshj as mgl,
tam.xianynhsh as xynhsh,
tam.xianynvhsh as xynvhsh,
taz.xingm as zx,
tax.xingb,
taz.chushrq as zc,
tazcnw.zhengcnw as zcnw,
--t.climename as zj,
decode(taz.juzhd, tagl.juzhd, tat.climename, tazj.xianxc) as zj,
decode(taz.hujd, tagl.juzhd, tat.climename, tazh.xianxc) as zh,
tacszl.cuoshzhl as mcszl,
tab.biyqshshj as mcssj,
tawcqyy.weicqxycshyy as mwcyy,
taz.sysid as zrenkid,
tam.sysid as mrenkid,
taf.sysid as frenkid
from pis_p_guanlshx tagl
left join pis_p_renk taz
on tagl.renkid = taz.sysid
left join pis_p_guanx tagm
on taz.sysid = tagm.guanxrid
and tagm.guanxlx in ('20', '30')
left join pis_p_renk tam
on tagm.renkid = tam.sysid
and tam.xingb = '2'
left join sys_clime tamj
on tamj.climecode = tam.juzhd
left join sys_clime tamh
on tamh.climecode = tam.hujd
left join pis_p_biysh tab
on tab.renkid = tam.sysid
and tab.dangq = '1'
left join pis_c_weicqxycshyy tawcqyy
on tawcqyy.code = tab.weicqxycshyy
left join pis_c_cuoshzhl tacszl
on tacszl.code = tab.cuoshzhl
left join pis_p_guanx tagf
on taz.sysid = tagf.guanxrid
and tagf.guanxlx in ('20', '30')
left join pis_p_renk taf
on tagf.renkid = taf.sysid
and taf.xingb = '1'
left join sys_clime tafj
on tafj.climecode = taf.juzhd
left join sys_clime tafh
on tafh.climecode = taf.hujd
left join sys_clime tazj
on tazj.climecode = taz.juzhd
left join sys_clime tazh
on tazh.climecode = taz.hujd
left join sys_clime tat
on tat.climecode = tagl.juzhd
left join pis_c_zhengcnw tazcnw
on tazcnw.code = taz.zhengcnw
left join pis_c_xingb tax
on tax.code = taz.xingb
left join pis_p_zhuxyy tazx
on (taz.sysid = tazx.renkid and tazx.zhuxyy in ('2', '3') or
taz.sysid is null)
left join pis_p_guanlshx tamgl
on tamgl.renkid = tam.sysid
where taz.chushrq between '2000' and '20100415'
and tagl.juzhd = '141032200201'
and tagl.dangq in ('1', '3')) ta
where ta.rn = 1)
select aa.*
from queryTable aa
left join (select NVL(zz.zzc, sss.zc) as zzzc, sss.zrenkid
from queryTable sss
left join (select ttt.frenkid, ttt.mrenkid, min(ttt.zc) zzc
from queryTable ttt
group by ttt.frenkid, ttt.mrenkid
having count(1) > 1) zz
on zz.frenkid = sss.frenkid
and zz.mrenkid = sss.mrenkid) bb
on aa.zrenkid = bb.zrenkid
order by to_number(case
when length(bb.zzzc) = 6 then
bb.zzzc || '01'
else
bb.zzzc
end) asc,
to_number(case
when length(aa.zc) = 6 then
aa.zc || '01'
else
aa.zc
end) asc,
aa.frenkid asc,
aa.mrenkid asc,
aa.zrenkid asc;
大家看看为了避免相同代码重复出现采用上面的别名,可是现在查询效率很低,帮忙调优一下。谢谢1
(select ta.zhubm as 户编码,
ta.zc,
ta.frenkid,
ta.mrenkid,
ta.zrenkid,
ta.fx as 男姓名,
ta.fj as 男方居住地,
ta.fh as 男方户籍地,
ta.mx as 女方姓名,
ta.mc as 女方出生年月,
ta.mj as 女方居住地,
ta.mh as 女方户籍地,
-- a.mgl as 女方管理属性变动时间,
ta.xynhsh as 现有男孩数,
ta.xynvhsh as 现有女孩数,
ta.zx as 姓名,
ta.xingb as 性别,
ta.zcnw as 政策内外,
ta.zc as 出生年月,
ta.zj as 现居住地,
ta.zh as 户籍所在地,
ta.mcszl as 措施类型,
ta.mcssj as 措施起始时间,
--a.mwcyy as 未采取措施原因,
'' as 备注
from (select row_number() OVER(PARTITION BY taz.sysid ORDER BY tam.sysid, taf.sysid) as rn,
nvl(taz.hubm, tam.hubm) as zhubm,
taf.xingm as fx,
decode(taf.juzhd, tagl.juzhd, tat.climename, tafj.xianxc) as fj,
decode(taf.hujd, tagl.juzhd, tat.climename, tafh.xianxc) as fh,
tam.xingm as mx,
tam.chushrq as mc,
decode(tam.juzhd, tagl.juzhd, tat.climename, tamj.xianxc) as mj,
decode(tam.hujd, tagl.juzhd, tat.climename, tamh.xianxc) as mh,
tamgl.guanlshxbdshj as mgl,
tam.xianynhsh as xynhsh,
tam.xianynvhsh as xynvhsh,
taz.xingm as zx,
tax.xingb,
taz.chushrq as zc,
tazcnw.zhengcnw as zcnw,
--t.climename as zj,
decode(taz.juzhd, tagl.juzhd, tat.climename, tazj.xianxc) as zj,
decode(taz.hujd, tagl.juzhd, tat.climename, tazh.xianxc) as zh,
tacszl.cuoshzhl as mcszl,
tab.biyqshshj as mcssj,
tawcqyy.weicqxycshyy as mwcyy,
taz.sysid as zrenkid,
tam.sysid as mrenkid,
taf.sysid as frenkid
from pis_p_guanlshx tagl
left join pis_p_renk taz
on tagl.renkid = taz.sysid
left join pis_p_guanx tagm
on taz.sysid = tagm.guanxrid
and tagm.guanxlx in ('20', '30')
left join pis_p_renk tam
on tagm.renkid = tam.sysid
and tam.xingb = '2'
left join sys_clime tamj
on tamj.climecode = tam.juzhd
left join sys_clime tamh
on tamh.climecode = tam.hujd
left join pis_p_biysh tab
on tab.renkid = tam.sysid
and tab.dangq = '1'
left join pis_c_weicqxycshyy tawcqyy
on tawcqyy.code = tab.weicqxycshyy
left join pis_c_cuoshzhl tacszl
on tacszl.code = tab.cuoshzhl
left join pis_p_guanx tagf
on taz.sysid = tagf.guanxrid
and tagf.guanxlx in ('20', '30')
left join pis_p_renk taf
on tagf.renkid = taf.sysid
and taf.xingb = '1'
left join sys_clime tafj
on tafj.climecode = taf.juzhd
left join sys_clime tafh
on tafh.climecode = taf.hujd
left join sys_clime tazj
on tazj.climecode = taz.juzhd
left join sys_clime tazh
on tazh.climecode = taz.hujd
left join sys_clime tat
on tat.climecode = tagl.juzhd
left join pis_c_zhengcnw tazcnw
on tazcnw.code = taz.zhengcnw
left join pis_c_xingb tax
on tax.code = taz.xingb
left join pis_p_zhuxyy tazx
on (taz.sysid = tazx.renkid and tazx.zhuxyy in ('2', '3') or
taz.sysid is null)
left join pis_p_guanlshx tamgl
on tamgl.renkid = tam.sysid
where taz.chushrq between '2000' and '20100415'
and tagl.juzhd = '141032200201'
and tagl.dangq in ('1', '3')) ta
where ta.rn = 1)
select aa.*
from queryTable aa
left join (select NVL(zz.zzc, sss.zc) as zzzc, sss.zrenkid
from queryTable sss
left join (select ttt.frenkid, ttt.mrenkid, min(ttt.zc) zzc
from queryTable ttt
group by ttt.frenkid, ttt.mrenkid
having count(1) > 1) zz
on zz.frenkid = sss.frenkid
and zz.mrenkid = sss.mrenkid) bb
on aa.zrenkid = bb.zrenkid
order by to_number(case
when length(bb.zzzc) = 6 then
bb.zzzc || '01'
else
bb.zzzc
end) asc,
to_number(case
when length(aa.zc) = 6 then
aa.zc || '01'
else
aa.zc
end) asc,
aa.frenkid asc,
aa.mrenkid asc,
aa.zrenkid asc;
大家看看为了避免相同代码重复出现采用上面的别名,可是现在查询效率很低,帮忙调优一下。谢谢1
想快都难,有时可考虑冗余数据来减少表之间关联,
以空间换时间!