做一个提成表:TiCheng:
Item_ID Job_ID TC_Money
(每一个项目的标识) (职位标识)提成金额
做一个视图
CREATE VIEW dbo.TC_DingE
AS
--定额提成
SELECT TOP 100 PERCENT dbo.Item.Item_ID, dbo.Employee.EM_ID,
dbo.TiCheng.TC_Money
FROM dbo.Employee INNER JOIN
dbo.Job ON dbo.Employee.Job_ID = dbo.Job.Job_ID RIGHT OUTER JOIN
dbo.TiCheng ON dbo.Job.Job_ID = dbo.TiCheng.Job_ID FULL OUTER JOIN
dbo.Item ON dbo.TiCheng.Item_ID = dbo.Item.Item_ID
WHERE (dbo.TiCheng.TC_Money >= 0)
ORDER BY dbo.Employee.EM_ID
视图2:
CREATE VIEW dbo.TC_EM_Money
--雇员特有的提成金额
AS
SELECT dbo.Item.Item_ID, dbo.Employee.EM_ID,
dbo.Employee.T_Money AS TC_money
FROM dbo.Employee INNER JOIN
dbo.Job ON dbo.Employee.Job_ID = dbo.Job.Job_ID RIGHT OUTER JOIN
dbo.TiCheng ON dbo.Job.Job_ID = dbo.TiCheng.Job_ID FULL OUTER JOIN
dbo.Item ON dbo.TiCheng.Item_ID = dbo.Item.Item_ID
WHERE (dbo.TiCheng.TC_Money = - 2)
视图3:
CREATE VIEW dbo.TC_EM_Percent
--按员工特有的提成百分比
AS
SELECT dbo.Item.Item_ID, dbo.Employee.EM_ID,
dbo.Employee.T_Percent * dbo.Item.price / 100 AS TC_money
FROM dbo.Employee INNER JOIN
dbo.Job ON dbo.Employee.Job_ID = dbo.Job.Job_ID RIGHT OUTER JOIN
dbo.TiCheng ON dbo.Job.Job_ID = dbo.TiCheng.Job_ID FULL OUTER JOIN
dbo.Item ON dbo.TiCheng.Item_ID = dbo.Item.Item_ID
WHERE (dbo.TiCheng.TC_Money = - 1)
整合3个视图的总提成视图:
CREATE VIEW dbo.TC_All_Kinds
AS
SELECT dbo.TC_DingE.*
FROM dbo.TC_DingE
union
SELECT *
FROM dbo.TC_EM_Money
union
SELECT *
FROM dbo.TC_EM_Percent
最后得出的视图:
CREATE VIEW dbo.TestBB
--总提成
AS
SELECT dbo.testA.*, dbo.TC_All_Kinds.TC_Money AS TC_Money
FROM dbo.testA INNER JOIN
dbo.TC_All_Kinds ON dbo.testA.Item_ID = dbo.TC_All_Kinds.Item_ID AND
dbo.testA.EM_ID = dbo.TC_All_Kinds.EM_ID