跨表查询

gdmjh870724 发布于 2012-04-06 14:43， 506 次点击

有两个数据表，a表和b表，我想按日期统计czonghe列的汇总结果，显示如下
表a：            表b：
cdate    czonghe        cdate    czonghe
2012-4-1    10        2012-4-2    20
2012-4-1    20        2012-4-2    50
2012-4-2    30        2012-4-3    74
2012-4-2    40        2012-4-3    11

怎么写sql语句得出下面的结果
日期    a表汇总    b表汇总
2012-4-1    30    0
2012-4-2    70    70
2012-4-3    0    85
万分感谢

2 回复

netlin2012-04-09 23:05

希望能帮到你！没时间做优化！

select isnull(aa.cdate,bb.cdate) as cdate,isnull(a_total,0) as a_total,isnull(b_total,0) as b_total from
    (select cdate,sum(czonghe) as a_total from a group by cdate) aa
    FULL OUTER JOIN
    (select cdate,sum(czonghe) as b_total from b group by cdate) bb
     ON aa.cdate = bb.cdate

[ 本帖最后由 netlin 于 2012-4-9 23:28 编辑 ]

netlin2012-04-09 23:20

上面的结果有问题，更正内容如下：
select isnull(aa.cdate,bb.cdate) as cdate,isnull(a_total,0) as a_total,isnull(b_total,0) as b_total from
    (select cdate,sum(czonghe) as a_total from a group by cdate) aa
    FULL OUTER JOIN
    (select cdate,sum(czonghe) as b_total from b group by cdate) bb
     ON aa.cdate = bb.cdate