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

[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‘财务部‘, N‘1701室‘, N‘负责公司整体财务进出账管理‘) GO INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (2, N‘技术部‘, N‘1702-1706室‘, N‘负责公司技术方案指定.开发.实施‘) GO INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (3, N‘人事部‘, N‘1707室‘, N‘负责公司员工招聘,福利待遇‘) GO INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (4, N‘行政部‘, N‘1708室‘, N‘负责公司办公设备采购,登记‘) GO INSERT [dbo].[Department] ([DepID], [DepName], [DepAddress], [DepJobContent]) VALUES (5, N‘业务部‘, N‘1709-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, N‘13666767556‘) GO INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (2, 2, N‘吕泽强‘, 1, N‘13666767556‘) GO INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (3, 3, N‘丁伟亮‘, 1, N‘13666767556‘) GO INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (4, 4, N‘马建‘, 0, N‘13666767556‘) GO INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (5, 5, N‘孙晓红‘, 0, N‘13666767556‘) GO INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (6, 1, N‘李玲‘, 0, N‘13666767556‘) GO INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (7, 2, N‘李子明‘, 0, N‘13666767556‘) GO INSERT [dbo].[Employees] ([EmpID], [DepID], [EmpName], [EmpSex], [EmpTel]) VALUES (8, 3, N‘马欢‘, 0, N‘13666767556‘) 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根据提成比率区间计算业绩提成

标签：维护   办公设备   employee   ddr   name   count   man   计算   _id