呃。。 写完了分析竟然自己想出来了,狂晕~ 我写的结果 select newid = isnull(a.id,b.id),newperiod=isnull(a.period,b.period),a.cost1,b.cost2 from (select id,period,sum(cost1) as cost1 from t1 group by period,id)as a full outer join (select id,period,sum(cost2) as cost2 from t2 group by period,id)as b on a.id = b.id and a.period = b.period order by newperiod
这是什么意思啊?
这样能让C这个业务员统计进来吗?
如果你的join 改成full out join的话,因为c业务员只显示在b.id一列,b业务员只显示在a.id一列,所以B、C业务员也不能同时统计进来
这个问题的难点就在这里!!我用了newid =isnull(t1.id,t2.id)同时收集a.id与b.id,
但是不能group by!
select t1.id,t1.period,sum(t1.cost1),t2.id,t2.period,sum(t2.cost2)
from t1 full join t2
on t1.bh = t2.bh
group by t1.period,t2.period
还有join的时候条件应该是a.id = b.id and a.period = b.period
可是这样的条件还不够,
因为a在0212期间的cost1有两条记录,而a在0212期间的cost2只有一条记录
join之后a的0212期间的cost2就变成两条一模一样的记录了!!所以这样去sum的话会出错的!!
如果想用join和sum加起来这种方法去统计cost,
应该是把t1,t2先各自sum一下,然后再join起来。而不是join起来再sum
请大家考虑一下我的问题分析。
写完了分析竟然自己想出来了,狂晕~
我写的结果
select newid = isnull(a.id,b.id),newperiod=isnull(a.period,b.period),a.cost1,b.cost2 from
(select id,period,sum(cost1) as cost1 from t1 group by period,id)as a full outer join
(select id,period,sum(cost2) as cost2 from t2 group by period,id)as b
on a.id = b.id and a.period = b.period
order by newperiod