首页 > 数据库技术 > 详细

SQLSERVER根据提成比率区间计算业绩提成

时间:2019-11-17 14:55:04      阅读:72      评论:0      收藏:0      [点我收藏+]
USE [Employee]
GO
/****** Object:  Table [dbo].[Commission]    Script Date: 2019/11/17 14:10:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Commission](
    [ComID] [int] IDENTITY(1,1) NOT NULL,
    [ComRatio] [float] NULL,
    [ComStartNum] [int] NULL,
    [ComEndNum] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [ComID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Department]    Script Date: 2019/11/17 14:10:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Department](
    [DepID] [int] IDENTITY(1,1) NOT NULL,
    [DepName] [varchar](50) NULL,
    [DepAddress] [varchar](50) NULL,
    [DepJobContent] [varchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
    [DepID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 2019/11/17 14:10:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [DepID] [int] NULL,
    [EmpName] [varchar](50) NULL,
    [EmpSex] [int] NULL,
    [EmpTel] [varchar](13) NULL,
PRIMARY KEY CLUSTERED 
(
    [EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Performance]    Script Date: 2019/11/17 14:10:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Performance](
    [PerID] [int] IDENTITY(1,1) NOT NULL,
    [EmpID] [int] NULL,
    [PerCount] [int] NULL,
    [PerContent] [varchar](200) NULL,
    [IsCommission] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [PerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Commission] ON 

GO
INSERT [dbo].[Commission] ([ComID], [ComRatio], [ComStartNum], [ComEndNum]) VALUES (1, 0.05, 0, 2000)
GO
INSERT [dbo].[Commission] ([ComID], [ComRatio], [ComStartNum], [ComEndNum]) VALUES (2, 0.1, 2001, 8000)
GO
INSERT [dbo].[Commission] ([ComID], [ComRatio], [ComStartNum], [ComEndNum]) VALUES (3, 0.15, 8001, 20000)
GO
INSERT [dbo].[Commission] ([ComID], [ComRatio], [ComStartNum], [ComEndNum]) VALUES (4, 0.2, 20001, 40000)
GO
INSERT [dbo].[Commission] ([ComID], [ComRatio], [ComStartNum], [ComEndNum]) VALUES (5, 0.25, 40001, 80000)
GO
INSERT [dbo].[Commission] ([ComID], [ComRatio], [ComStartNum], [ComEndNum]) VALUES (6, 0.3, 80001, 120000)
GO
INSERT [dbo].[Commission] ([ComID], [ComRatio], [ComStartNum], [ComEndNum]) VALUES (7, 0.35, 120000, 2000000)
GO
SET IDENTITY_INSERT [dbo].[Commission] OFF
GO
SET IDENTITY_INSERT [dbo].[Department] ON 

GO
INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (1, N财务部, N1701室, N负责公司整体财务进出账管理)
GO
INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (2, N技术部, N1702-1706室, N负责公司技术方案指定.开发.实施)
GO
INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (3, N人事部, N1707室, N负责公司员工招聘,福利待遇)
GO
INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (4, N行政部, N1708室, N负责公司办公设备采购,登记)
GO
INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (5, N业务部, N1709-1719室, N负责公司产品的销售和客户的拓展,维护)
GO
SET IDENTITY_INSERT [dbo].[Department] OFF
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 

GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (1, 1, N刘忠田, 1, N13666767556)
GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (2, 2, N吕泽强, 1, N13666767556)
GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (3, 3, N丁伟亮, 1, N13666767556)
GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (4, 4, N马建, 0, N13666767556)
GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (5, 5, N孙晓红, 0, N13666767556)
GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (6, 1, N李玲, 0, N13666767556)
GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (7, 2, N李子明, 0, N13666767556)
GO
INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (8, 3, N马欢, 0, N13666767556)
GO
SET IDENTITY_INSERT [dbo].[Employees] OFF
GO
SET IDENTITY_INSERT [dbo].[Performance] ON 

GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (1, 5, 3000, N某产品10公斤, 1)
GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (2, 6, 7000, N某产品50公斤, 1)
GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (3, 7, 9000, N某产品150公斤, 1)
GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (4, 8, 10000, N某产品250公斤, 1)
GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (5, 8, 13000, N某产品250公斤, 1)
GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (6, 8, 150000, N某产品250公斤, 1)
GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (7, 2, 2000, N公司产品管理系统开发奖金, 0)
GO
INSERT [dbo].[Performance] ([PerID], [EmpID], [PerCount], [PerContent], [IsCommission]) VALUES (8, 3, 2000, N某产品公司产品管理系统开发奖金公斤, 0)
GO
SET IDENTITY_INSERT [dbo].[Performance] OFF
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD FOREIGN KEY([DepID])
REFERENCES [dbo].[Department] ([DepID])
GO
ALTER TABLE [dbo].[Performance]  WITH CHECK ADD FOREIGN KEY([EmpID])
REFERENCES [dbo].[Employees] ([EmpID])
GO

SELECT b.EmpName AS 员工姓名, b.PerCount AS 业绩,b.ComRatio AS 提成比例,b.PerCount * b.ComRatio AS 提成金额
FROM (
SELECT a.EmpName,a.PerCount
,(SELECT ComRatio FROM dbo.Commission WHERE a.perCount BETWEEN ComStartNum AND ComEndNum) AS ComRatio
FROM(
SELECT b.EmpName,SUM(a.PerCount) AS PerCount
FROM dbo.Performance a INNER JOIN dbo.Employees b
ON a.EmpID = b.EmpID
GROUP BY b.EmpName
) a
) b
ORDER BY b.ComRatio

 

SQLSERVER根据提成比率区间计算业绩提成

原文:https://www.cnblogs.com/superfeeling/p/11876287.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!