b表语句改为这样 (SELECT CAST(to_date('2014-1-31','yyyy-mm-dd') AS DATE) AS datee,dwbh,xlbh,SUM(ic) AS icm,SUM(tb) AS tbm FROM (SELECT dwbh,xlbh,xfjee AS IC,nvl(0,0) AS TB FROM ic_xfhzk WHERE datee BETWEEN to_date('2014-1-1','yyyy-mm-dd') AND to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300') UNION ALL SELECT dwbh,xlbh,nvl(0,0) AS IC,xfjee AS TB FROM ic_xjhzk WHERE datee BETWEEN to_date('2014-1-1','yyyy-mm-dd') AND to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300') ) GROUP BY dwbh,xlbh)
a表的datee,在结果中没有体现,可以只按dwbh,xlbh两个分组即可 (SELECT dwbh,xlbh,nvl(SUM(CASE WHEN sjlx='01'THEN xfjee END),0) AS A,nvl(SUM(CASE WHEN sjlx='A4'THEN xfjee END),0) AS C,nvl(SUM(CASE WHEN sjlx='B9'THEN xfjee END),0) AS I,nvl(SUM(CASE WHEN sjlx='A5'THEN xfjee END),0) AS D,nvl(SUM(CASE WHEN sjlx='30'THEN xfjee END),0) AS T,nvl(SUM(CASE WHEN sjlx='80'THEN xfjee END),0) AS TB FROM (SELECT dwbh,xlbh,sjlx,xfjee FROM ic_xfhzk WHERE datee = to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300') UNION ALL SELECT dwbh,xlbh,sjlx,xfjee FROM ic_xjhzk WHERE datee = to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300')) GROUP BY dwbh,xlbh)
(SELECT CAST(to_date('2014-1-31','yyyy-mm-dd') AS DATE) AS datee,dwbh,xlbh,SUM(ic) AS icm,SUM(tb) AS tbm FROM (SELECT dwbh,xlbh,xfjee AS IC,nvl(0,0) AS TB FROM ic_xfhzk WHERE datee BETWEEN to_date('2014-1-1','yyyy-mm-dd') AND to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300')
UNION ALL
SELECT dwbh,xlbh,nvl(0,0) AS IC,xfjee AS TB FROM ic_xjhzk WHERE datee BETWEEN to_date('2014-1-1','yyyy-mm-dd') AND to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300')
) GROUP BY dwbh,xlbh)
(SELECT dwbh,xlbh,nvl(SUM(CASE WHEN sjlx='01'THEN xfjee END),0) AS A,nvl(SUM(CASE WHEN sjlx='A4'THEN xfjee END),0) AS C,nvl(SUM(CASE WHEN sjlx='B9'THEN xfjee END),0) AS I,nvl(SUM(CASE WHEN sjlx='A5'THEN xfjee END),0) AS D,nvl(SUM(CASE WHEN sjlx='30'THEN xfjee END),0) AS T,nvl(SUM(CASE WHEN sjlx='80'THEN xfjee END),0) AS TB
FROM
(SELECT dwbh,xlbh,sjlx,xfjee FROM ic_xfhzk WHERE datee = to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300')
UNION ALL
SELECT dwbh,xlbh,sjlx,xfjee FROM ic_xjhzk WHERE datee = to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300'))
GROUP BY dwbh,xlbh)