有三个表: 员工表 编号 姓名 .......
服务表 单据编号 .....业务员工号....
销售表 产品编号 ......业务员工号
业务员工号就是员工编号
我想统计每个员工的业务次数,产生一个新表:
员工编号 姓名 服务业务次数 销售业务次数 业务总数我写的如下:select employee.员工编号,employee.姓名,
(select count(*/*reply.单据编号*/) as 服务业务次数
from employee left join reply
on employee.员工编号=reply.业务员工号
group by reply.业务员工号),
(select count(*) as 销售业务次数
from employee left join productsell
on employee.员工编号=productsell.业务员工号
group by productsell.业务员工号)
/*into calculate *//*reply.单据编号*/
from employeeERROR: Single row subquery producted more than one row.
into calculate 也有问题,不能用
服务表 单据编号 .....业务员工号....
销售表 产品编号 ......业务员工号
业务员工号就是员工编号
我想统计每个员工的业务次数,产生一个新表:
员工编号 姓名 服务业务次数 销售业务次数 业务总数我写的如下:select employee.员工编号,employee.姓名,
(select count(*/*reply.单据编号*/) as 服务业务次数
from employee left join reply
on employee.员工编号=reply.业务员工号
group by reply.业务员工号),
(select count(*) as 销售业务次数
from employee left join productsell
on employee.员工编号=productsell.业务员工号
group by productsell.业务员工号)
/*into calculate *//*reply.单据编号*/
from employeeERROR: Single row subquery producted more than one row.
into calculate 也有问题,不能用
(select count(reply.单据编号) as 服务业务次数
from reply ),
(select count(*) as 销售业务次数
from prodecuctsell),
sum((select count(reply.单据编号) as 服务业务次数
from reply )+(select count(*) as 销售业务次数 from prodecuctsell)) 总业务数
from employee
where employee.员工编号= reply.业务员工号 and
employee.员工编号= prodecuctsell.业务员工号
left outer join (select cnt=count(id),id from reply) as b on a.id=b.id
left outer join (select cnt=count(id),id from productsell) as c on a.id=b.id试一下没测试过
例:
(select count(0) as 服务业务次数
from employee where
employee.员工编号=reply.业务员工号),
我要求统计每个人的业务,即使他的业务是0一项业务就生成一个 单据编号
left outer join (select cnt=count(id),id from reply group by id) as b on a.id=b.id
left outer join (select cnt=count(id),id from productsell group by id) as c on a.id=b.id
SQL Server可用