我想吧这四条代码合成一条怎么合 啊
能合成的话能给和好后的给我吗
谢谢了
left join ( --统计
--覆盖方式ADSL交换区号无PSTN
select fldex9, count(*) as n
from tar_station t
/*where not exists (SELECT 1
FROM TAR_RELTION_SWAPAREA_STATION
where t.fldcbriefname = BUILDING_CODE)*/
WHERE NOT exists (select 1
from TAR_RELTION_SWAPAREA_STATION
where SWAPAREA_TYPE = 'PSTN'
and building_code = T.FLDCBRIEFNAME)
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and t.fldex29 LIKE '%3%' --带ADSL
and (t.FLDEX27 != '1' --不含--用户线接入介质为无用户线接入介质
or t.FLDEX27 is null)
AND T.FLDCNAME NOT LIKE '%未竣工%'
AND T.FLDCNAME NOT LIKE '%未覆盖%'
group by fldex9) b
on a.fldareaid = b.fldex9 left join (
-- 覆盖方式不是ADSL交换区号含PSTN
select fldex9, count(*) as n
from tar_station t,
(select distinct w.building_code
from TAR_RELTION_SWAPAREA_STATION w
where regexp_like(w.swaparea_type, 'PSTN', 'i')) s
where t.fldex9 is not null
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and t.fldex29 not like '%3%' --覆盖方式不带 ADSL
and t.fldcbriefname = s.building_code
group by t.fldex9) c
on a.fldareaid = c.fldex9 left join ( ----带fttb或ftth 无Ag或无pon
select fldex9, count(*) as n
from tar_station t
WHERE NOT exists (select 1
from TAR_RELTION_SWAPAREA_STATION
where SWAPAREA_TYPE = 'AG'
and building_code = T.FLDCBRIEFNAME)
AND NOT exists
(select 1
from TAR_RELTION_SWAPAREA_STATION
where SWAPAREA_TYPE = 'PON'
and building_code = T.FLDCBRIEFNAME)
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and (t.fldex29 LIKE '%4%' OR t.fldex29 LIKE '%5%')
and (t.FLDEX27 != '1' --不含--用户线接入介质为无用户线接入介质
or t.FLDEX27 is null)
AND T.FLDCNAME NOT LIKE '%未竣工%'
AND T.FLDCNAME NOT LIKE '%未覆盖%'
group by fldex9) d
on a.fldareaid = d.fldex9 left join ( --不带fttb且不带ftth有ag或有pon
select fldex9, count(*) as n
from tar_station t,
(select distinct w.building_code
from TAR_RELTION_SWAPAREA_STATION w
where (regexp_like(w.swaparea_type, 'PON', 'i') or
(regexp_like(w.swaparea_type, 'AG', 'i')))) s
where t.fldex9 is not null
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and (t.fldex29 not LIKE '%4%' and t.fldex29 not LIKE '%5%')
and t.fldcbriefname = s.building_code
group by t.fldex9) e
on a.fldareaid = e.fldex9
能合成的话能给和好后的给我吗
谢谢了
left join ( --统计
--覆盖方式ADSL交换区号无PSTN
select fldex9, count(*) as n
from tar_station t
/*where not exists (SELECT 1
FROM TAR_RELTION_SWAPAREA_STATION
where t.fldcbriefname = BUILDING_CODE)*/
WHERE NOT exists (select 1
from TAR_RELTION_SWAPAREA_STATION
where SWAPAREA_TYPE = 'PSTN'
and building_code = T.FLDCBRIEFNAME)
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and t.fldex29 LIKE '%3%' --带ADSL
and (t.FLDEX27 != '1' --不含--用户线接入介质为无用户线接入介质
or t.FLDEX27 is null)
AND T.FLDCNAME NOT LIKE '%未竣工%'
AND T.FLDCNAME NOT LIKE '%未覆盖%'
group by fldex9) b
on a.fldareaid = b.fldex9 left join (
-- 覆盖方式不是ADSL交换区号含PSTN
select fldex9, count(*) as n
from tar_station t,
(select distinct w.building_code
from TAR_RELTION_SWAPAREA_STATION w
where regexp_like(w.swaparea_type, 'PSTN', 'i')) s
where t.fldex9 is not null
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and t.fldex29 not like '%3%' --覆盖方式不带 ADSL
and t.fldcbriefname = s.building_code
group by t.fldex9) c
on a.fldareaid = c.fldex9 left join ( ----带fttb或ftth 无Ag或无pon
select fldex9, count(*) as n
from tar_station t
WHERE NOT exists (select 1
from TAR_RELTION_SWAPAREA_STATION
where SWAPAREA_TYPE = 'AG'
and building_code = T.FLDCBRIEFNAME)
AND NOT exists
(select 1
from TAR_RELTION_SWAPAREA_STATION
where SWAPAREA_TYPE = 'PON'
and building_code = T.FLDCBRIEFNAME)
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and (t.fldex29 LIKE '%4%' OR t.fldex29 LIKE '%5%')
and (t.FLDEX27 != '1' --不含--用户线接入介质为无用户线接入介质
or t.FLDEX27 is null)
AND T.FLDCNAME NOT LIKE '%未竣工%'
AND T.FLDCNAME NOT LIKE '%未覆盖%'
group by fldex9) d
on a.fldareaid = d.fldex9 left join ( --不带fttb且不带ftth有ag或有pon
select fldex9, count(*) as n
from tar_station t,
(select distinct w.building_code
from TAR_RELTION_SWAPAREA_STATION w
where (regexp_like(w.swaparea_type, 'PON', 'i') or
(regexp_like(w.swaparea_type, 'AG', 'i')))) s
where t.fldex9 is not null
and t.fldisfilling = 1 -- 1填报
and t.fldbuildstatus not in ('3', '5') -- 3拆迁 5拆除
and nvl(t.isfw, ' ') <> '1' --非封网
and (t.fldex29 not LIKE '%4%' and t.fldex29 not LIKE '%5%')
and t.fldcbriefname = s.building_code
group by t.fldex9) e
on a.fldareaid = e.fldex9
select fldex9, count(*) as n from B表
union all
select fldex9, count(*) as n from C表
union all
select fldex9, count(*) as n from D表
union all
select fldex9, count(*) as n from E表)
) tmp
where a.fldareaid = tmp.fldex9