做连接就可以了。select a.cby,a.yccmhs,a.ycdlhs,b.SCCMHS,b.SCDLHS
(
SELECT a.cby,NVL(count(decode(b.ELECPROPERTY,'照明','照明')),0) as YCCMHS,NVL(count(decode(b.ELECPROPERTY,'动力','动力')),0) AS YCDLHS
FROM TB_DLDF_JLJBXXB a,TB_YKBZ_CLIENTARCHIVES b
WHERE a.hh=b.usno AND a.NF='2003' AND a.YF='12' group by a.cby
) a,
(
SELECT c.cby,NVL(count(decode(d.ELECPROPERTY,'照明','照明')) ,0) AS SCCMHS,NVL(count(decode(d.ELECPROPERTY,'动力','动力')),0) AS SCDLHS
FROM TB_DLDF_JLJBXXB c,TB_YKBZ_CLIENTARCHIVES d
WHERE c.hh=d.usno and c.CBZT='抄表' AND a.NF='2003' AND a.YF='12'group by c.cby)
) b
where a.CBY = b.CBY上面的例子是全匹配的结果。
如果两个对应的CBY记录数不同,可以使用左关联或右关联。
9i支持(left join 和 right join)8i则只能用(+)
在9i里还支持FULL OUTER JOIN,8i就要另外想办法了。还是9i好啊。
(
SELECT a.cby,NVL(count(decode(b.ELECPROPERTY,'照明','照明')),0) as YCCMHS,NVL(count(decode(b.ELECPROPERTY,'动力','动力')),0) AS YCDLHS
FROM TB_DLDF_JLJBXXB a,TB_YKBZ_CLIENTARCHIVES b
WHERE a.hh=b.usno AND a.NF='2003' AND a.YF='12' group by a.cby
) a,
(
SELECT c.cby,NVL(count(decode(d.ELECPROPERTY,'照明','照明')) ,0) AS SCCMHS,NVL(count(decode(d.ELECPROPERTY,'动力','动力')),0) AS SCDLHS
FROM TB_DLDF_JLJBXXB c,TB_YKBZ_CLIENTARCHIVES d
WHERE c.hh=d.usno and c.CBZT='抄表' AND a.NF='2003' AND a.YF='12'group by c.cby)
) b
where a.CBY = b.CBY上面的例子是全匹配的结果。
如果两个对应的CBY记录数不同,可以使用左关联或右关联。
9i支持(left join 和 right join)8i则只能用(+)
在9i里还支持FULL OUTER JOIN,8i就要另外想办法了。还是9i好啊。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货