---------------------业务员业绩报表----------------------
create proc p4
as
--该存储过程求哪一个营业员签订的订单最多以及与此业务员签订订单的所有客户资料
begin
declare @eid int
declare @re table(eid int,count1 int)
insert @re select employeeid,count(*) from orders group by employeeid
--将各个业务员签订的订单数插入@re
set @eid=(select eid from @re where count1=(select max(count1) from @re ))
--将最多的业务员编号输入@eid
print convert(varchar,@eid) + '号业务员签订的订单最多'
--输出此业务员的编号
select * from employees where employeeid=@eid
--显示此业务员的详细资料
select * from orders as a right join customers as b on a.customerid=b.customerid and a.employeeid=@eid
--显示与此业务员签订订单的客户资料
end
--drop proc p4
exec p4
---------------------------------------------------我就是无情冷酷的分隔线----------------------------------------------------
create proc p4
as
--该存储过程求哪一个营业员签订的订单最多以及与此业务员签订订单的所有客户资料
begin
declare @eid int
declare @re table(eid int,count1 int)
insert @re select employeeid,count(*) from orders group by employeeid
--将各个业务员签订的订单数插入@re
set @eid=(select eid from @re where count1=(select max(count1) from @re ))
--将最多的业务员编号输入@eid
print convert(varchar,@eid) + '号业务员签订的订单最多'
--输出此业务员的编号
select * from employees where employeeid=@eid
--显示此业务员的详细资料
select * from orders as a right join customers as b on a.customerid=b.customerid and a.employeeid=@eid
--显示与此业务员签订订单的客户资料
end
--drop proc p4
exec p4
---------------------------------------------------我就是无情冷酷的分隔线----------------------------------------------------