34,587
社区成员
发帖
与我相关
我的任务
分享
最好给出完整的表结构,测试数据,计算方法和正确结果.
select
f.工编号,f.部门编号,f.部门名称,
sum(a.金额) as A业务汇总,
count(a.客户号) as A业务笔数,
sum(b.金额) as B业务汇总,
count(b.客户号) as B业务笔数,
sum(c.金额) as C业务汇总,
count(c.客户号) as C业务笔数,
sum(d.金额) as D业务汇总,
count(d.客户号) as D业务笔数
from
A业务表 a,B业务表 b,C业务表 c, D业务表 d,员工客户关系表 e,员工信息表 f
where
a.客户号=e.客户号 and b.客户号=e.客户号 and c.客户号=e.客户号 and d.客户号=e.客户号 and e.员工编号=f.员工编号
group by
f.工编号,f.部门编号,f.部门名称
select e.员工编号,e.部门编号,e.部门名称,
a.s as A业务汇总,a.cnt as A业务笔数,
b.s as B业务汇总,b.cnt as B业务笔数,
c.s as c业务汇总,c.cnt as c业务笔数,
d.s as d业务汇总,d.cnt as d业务笔数
from 员工信息表 e
left join (select 员工编号,sum(金额) as s,count(*) as cnt from A业务表 inner join 员工客户关系表 on A业务表.客户号=员工客户关系表.客户号 group by 员工编号) a on e.员工编号=a.员工编号
left join (select 员工编号,sum(金额) as s,count(*) as cnt from b业务表 inner join 员工客户关系表 on b业务表.客户号=员工客户关系表.客户号 group by 员工编号) b on e.员工编号=b.员工编号
left join (select 员工编号,sum(金额) as s,count(*) as cnt from c业务表 inner join 员工客户关系表 on c业务表.客户号=员工客户关系表.客户号 group by 员工编号) c on e.员工编号=c.员工编号
left join (select 员工编号,sum(金额) as s,count(*) as cnt from d业务表 inner join 员工客户关系表 on d业务表.客户号=员工客户关系表.客户号 group by 员工编号) d on e.员工编号=d.员工编号