Ms SQL查询分组并合并两个表,统计两表

2019-04-29 更新,添加更详细的说明

先看效果,如下图,这是Ms sql语句,其它数据库库的语句也差不多大同小异

//sql语句
--原创来自 http://www.luofenming.com/show.aspx?id=ART2018120100001
select fh,name,sfxm,sum(ISNULL(je,0)) as je,sum(ISNULL(ssje,0)) as ssje from 
(select fh,name,sfxm, sum(je)as je, null as ssje from scfy  group by fh ,name,sfxm
union all
select fh,name,sfxm,null as je, sum(ssje)as ssje from sf  group by fh ,name,sfxm) 
tempTable group by fh,name,sfxm
Sql语句解析
1、先把表一和表二 (合并,统计 、分组)查询,执行代码是
select fh,name,sfxm, sum(je)as je, null as ssje from scfy  group by fh ,name,sfxm
union all
select fh,name,sfxm,null as je, sum(ssje)as ssje from sf  group by fh ,name,sfxm
执行结果如下图


2、再把合并之后的表 进行 分组 统计查询 执行代码

select fh,name,sfxm,sum(ISNULL(je,0)) as je,sum(ISNULL(ssje,0)) as ssje from tempTable group by fh,name,sfxm
--tempTable 为第一步查询出来的临时表  注意 sum(ISNULL(je,0))相加一定要是数字类型
执行结果如下图

评论

  1. 39.149.90.*2020-10-26 22:30:33