select e.雇员名,(select count(*) from 订单 where 订单ID=e.订单ID) as 订单数量,
sum(e.total) as total from select c.*,d.数量*d.单价 as total from
(select a.雇员名,b.订单ID from 业务表 a,订单 b
where a.雇员ID=b.雇员ID) c,订单明细 d
where c.订单ID=d.订单ID )e group by e.total,e.雇员名,e.订单ID
--太简单了吧?
select b.雇员名
,订单总数=count(distinct a.订单ID)
,订单总金额=sum(c.数量*c.单价)
from 订单 a
join 业务员 b on a.雇员ID=b.业务员
join 订单明细 c on a.订单ID=c.订单ID
group by b.雇员名
select
雇员名 as 业务员名,
isnull(订单总数,0) as 订单总数,
isnull(订单总金额,0) as 订单总金额
from
(业务员 left join
(select 雇员ID,count(*) as 订单总数 from 订单 group by 雇员ID) tO on 业务员.雇员ID=tO.雇员ID) left join
(select 雇员ID,sum(数量*单价) as 订单总金额 from 订单,订单明细 where 订单.订单ID=订单明细.订单ID group by 雇员ID) tS on 业务员.雇员ID=tS.雇员ID
select a.雇员ID,c.雇员名,count(distinct b.订单ID) 订单总数,sum(b.数量*b.单价) 订单总金额 from 订单 a,订单明细 b,业务员 c where a.订单ID=b.订单ID and a.雇员ID=c.雇员ID group by a.雇员ID,c.雇员名