日期:2014-05-17 浏览次数:20490 次
select a.经办人,sum(a.库存金额)库存金额,sum(b.销售金额)销售金额 from A表 a, B表 b where a.经办人 = b.经办人 and a.id=b.id/*注:两个表之间应该还有别的相连的关系,让A表的500对应B表的50而你没有列出来,如果实在没有,就需要给他们编号,对应编号相连*/
------解决方案--------------------
select case when a.经办人 IS null then b.经办人 else a.经办人 end as 经办人,a.库存金额 ,b.销售金额 from
(
select 经办人, SUM(库存金额) as 库存金额 from a group by 经办人
) a
full out join
(
select 经办人, SUM(销售金额) as 销售金额 from b group by 经办人
)b
on a.经办人=b.经办人
------解决方案--------------------
create table ta (库存金额 float, 经办人 nvarchar(20), ) create table tb (销售金额 float, 经办人 nvarchar(20), ) insert ta select 500, '小刘' union all select 400,'小刘' union all select 300,'小张' insert into tb select 50, '小刘' union all select 40,'小刘' union all select 30,'小张' select case a.经办人 when null then b.经办人 else a.经办人 end as 经办人,a.库存金额, b.销售金额 from ( select SUM (库存金额) 库存金额 ,经办人 from ta group by 经办人 ) a full join ( select SUM (销售金额) 销售金额 ,经办人 from tb group by 经办人 ) b on a.经办人=b.经办人 drop table ta drop table tb
------解决方案--------------------
select a.经办人,sum(a.库存金额) as '库存金额',sum(b.销售金额) as '销售金额' from a inner join b on a.经办人=b.经办人 group by a.经办人
------解决方案--------------------
select a.经办人,sum(a.库存金额)as 库存金额,sum(b.销售金额)as 销售金额 from A表 a, B表 b where a.经办人 = b.经办人 group by a.经办人
------解决方案--------------------
select a.经办人, sum(a.库存金额), sum(b.销售金额) from a left join b on a.经办人=b.经办人 group by a.经办人