SQL语句练习实例之八——对于销售员提成收入的计算

----对于销售员提成收入的计算
Create Table Emplyees
(
empid int not null,
empName nvarchar(20) not null
)
insert Emplyees
select 1,'张三'
union
select 2,'李四'
union
select 3 ,'王五'
go
create table Bills
(
empid int not null,
BillDate datetime not null,
BillRate decimal(5,2)
)
insert Bills
select 1,'2009-1-1',25 union
select 2,'2010-1-1',15 union
select 3,'2011-1-1',20 union
select 1,'2011-1-1',30 union
select 2,'2011-1-1',35

go
create table HoursWorked
(
jobid int not null,
empid int not null,
workDate datetime not null,
BillHours decimal(5,2)
)

insert HoursWorked
select 4,1,'2011-2-1',3 union
select 4,1,'2011-3-1',5 union
select 4,2,'2011-4-1',2 union
select 4,1,'2011-5-1',4

go
---需要一个查询，用来实现显示某个促销工作的促销员的姓名和总提成。总提成按每个促销员分别计算
----方法是工作小时数乘以适用的每小时收费标准。
---方法一、使用视图
create view HourRateRpt
as
select h1.empid,empname,workdate,billhours,
(select billRate from Bills as s1
where BillDate=(select MAX(billdate) from Bills as s2
where s2.BillDate<=h1.workdate and s1.empid=s2.empid
and s1.empid=h1.empid)) as billRate
from HoursWorked h1,Emplyees e1
where e1.empid=h1.empid

go
select empid,empName,SUM(BillHours*billRate) as billTotsal
from HourRateRpt
group by empid,empName

---方法二，使用一条SQL语句实现
---
go
--select e1.empid,e1.empName,SUM(billHours*
--(select billRate from Bills  as b1
-- where BillDate=(select max(billdate) from bills as b2
-- where b2.BillDate<=h1.workDate and b1.empid=b2.empid and b1.empid=h1.empid)))
--from HoursWorked h1,Emplyees e1
--where h1.empid=e1.empid
--group by e1.empid,e1.empName
go
select e1.empid,e1.empName,SUM(billHours*b1.BillRate)

from HoursWorked h1,Emplyees e1 ,Bills as b1
where e1.empid=b1.empid and e1.empid=h1.empid and
BillDate=(select max(billdate) from bills b2 where b2.empid=e1.empid and b2.BillDate<=h1.workDate)
and h1.workDate>=b1.BillDate
group by e1.empid,e1.empName
go
drop table Emplyees;
drop table HoursWorked;
drop table Bills;
drop view HourRateRpt