以下是俩个表
人员表 提成级别
EmployeeId EmployeeName profit floor 名称 范围(小) 范围(大) 提成
1 wangjian 100000 30000 第一阶段 30000 50000 8%
2 duyalin 80000 30000 第二阶段 50000 70000 10%
第三阶段 70000 90000 12%
第四阶段 90000 1000000 15%例如wangjian的利润是10万,保底是3万,他的提成应该是出去保底金额还剩下7万,而这7万有2万在第一阶段,2万在第二阶段,2万在第三阶段,1万在第四阶段,提成是 (20000*8%)+(20000*10%)+(20000*12%)+(10000*15%) = 7500关键是如何判断,好啰嗦,希望有心人帮忙解决下
人员表 提成级别
EmployeeId EmployeeName profit floor 名称 范围(小) 范围(大) 提成
1 wangjian 100000 30000 第一阶段 30000 50000 8%
2 duyalin 80000 30000 第二阶段 50000 70000 10%
第三阶段 70000 90000 12%
第四阶段 90000 1000000 15%例如wangjian的利润是10万,保底是3万,他的提成应该是出去保底金额还剩下7万,而这7万有2万在第一阶段,2万在第二阶段,2万在第三阶段,1万在第四阶段,提成是 (20000*8%)+(20000*10%)+(20000*12%)+(10000*15%) = 7500关键是如何判断,好啰嗦,希望有心人帮忙解决下
create table emp(EmployeeId int,EmployeeName varchar(15),profit int,[floor] int)
insert into emp
select 1,'wangjian',100000,30000 union all
select 2,'duyalin',80000,30000
gocreate table lev(lName varchar(15),min_N int,max_N int,ret varchar(10))
insert into lev
select '第1阶段',30000,50000,'8%' union all
select '第2阶段',50000,70000,'10%' union all
select '第3阶段',70000,90000,'12%' union all
select '第4阶段',90000,100000,'15%'
goselect a.EmployeeId,a.EmployeeName,sum(
cast(cast(left(b.ret,charindex('%',b.ret)-1) as int)*1./100 as decimal(6,2))*
(case when a.profit >= b.max_N then (b.max_n - b.min_N) else (a.profit - b.min_N) end)) as 提成
from emp a,lev b
where b.min_N >= a.[floor] and b.max_N <= a.profit
group by a.EmployeeId,a.EmployeeName
order by a.EmployeeIddrop table emp,lev/************EmployeeId EmployeeName 提成
----------- --------------- ---------------------------------------
1 wangjian 7500.00
2 duyalin 3600.00(2 行受影响)
人员表
EmployeeId EmployeeName profit floor
1 wangjian 100000 30000
2 duyalin 80000 20000 提成级别表
lName min_N max_N ret EmployeeId
第一阶段 30000 50000 8% 1
第二阶段 50000 70000 10% 1
第三阶段 70000 90000 12% 1
第四阶段 90000 1000000 15% 1
第一阶段 20000 30000 8% 2
第二阶段 30000 40000 10% 2
第三阶段 40000 50000 12% 2
第四阶段 50000 1000000 15% 2例如wangjian的利润是10万,保底是3万,他的提成应该是出去保底金额还剩下7万,而这7万有2万在第一阶段,2万在第二阶段,2万在第三阶段,1万在第四阶段,提成是 (20000*8%)+(20000*10%)+(20000*12%)+(10000*15%) = 7500
create table 人员表(EmployeeId int,EmployeeName varchar(10), profit int, floor int)
insert 人员表
select 1 ,'wangjian', 100000 ,30000 union all
select 2 ,'duyalin', 80000 ,20000 create table 提成级别表(lName varchar(10), min_N int, max_N int,ret varchar(5),EmployeeId int)
insert 提成级别表
select '第一阶段' ,30000 ,50000 ,'8%', 1 union all
select '第二阶段' ,50000 ,70000 ,'10%', 1 union all
select '第三阶段' ,70000 ,90000 ,'12%', 1 union all
select '第四阶段' ,90000 ,1000000 ,'15%', 1 union all
select '第一阶段' ,20000 ,30000 ,'8%', 2 union all
select '第二阶段' ,30000 ,40000 ,'10%', 2 union all
select '第三阶段' ,40000 ,50000 ,'12%', 2 union all
select '第四阶段' ,50000 ,1000000 ,'15%', 2 select a.EmployeeId,a.EmployeeName,a.profit,a.floor,
sum(case when profit>=max_N then (max_N-min_N)*cast(replace(ret,'%','') as int)/100.0
when profit>=min_N then (profit-min_N)*cast(replace(ret,'%','') as int)/100.0
else 0 end) as 提成
from 人员表 a,提成级别表 b
where a.EmployeeId=b.EmployeeId
group by a.EmployeeId,a.EmployeeName,a.profit,a.floor/*
EmployeeId EmployeeName profit floor 提成
----------- ------------ ----------- ----------- ------------
1 wangjian 100000 30000 7500.000000
2 duyalin 80000 20000 7500.000000(所影响的行数为 2 行
*/
duyalin的利润是8万,保底是2万,他的提成应该是出去保底金额还剩下6万,而这6万有1万在第一阶段,1万在第二阶段,1万在第三阶段,1万在第四阶段,提成是 (10000*8%)+(10000*10%)+(10000*12%)+(10000*15%) = 3500
而这6万有1万在第一阶段,1万在第二阶段,1万在第三阶段,3万在第四阶段,
提成是 (10000*8%)+(10000*10%)+(10000*12%)+(30000*15%)
=800+1000+1200+4500=7500看5楼的效果
select a.EmployeeId,a.EmployeeName,sum(
cast(cast(left(b.ret,charindex('%',b.ret)-1) as int)*1./100 as decimal(6,2))*
(case when a.profit >= b.max_N then (b.max_n - b.min_N) else (a.profit - b.min_N) end)) as 提成
from emp a,lev b
where b.min_N >= a.[floor] and b.max_N <= a.profit
and a.EmployeeId = b.EmployeeId
group by a.EmployeeId,a.EmployeeName
order by a.EmployeeId
insert 人员表
select 1 ,'wangjian', 100000 ,30000 union all
select 2 ,'duyalin', 80000 ,20000 create table 提成级别表(lName varchar(10), min_N int, max_N int,ret varchar(5),EmployeeId int)
insert 提成级别表
select '第一阶段' ,30000 ,50000 ,'8%', 1 union all
select '第二阶段' ,50000 ,70000 ,'10%', 1 union all
select '第三阶段' ,70000 ,90000 ,'12%', 1 union all
select '第四阶段' ,90000 ,1000000 ,'15%', 1 union all
select '第一阶段' ,20000 ,30000 ,'8%', 2 union all
select '第二阶段' ,30000 ,40000 ,'10%', 2 union all
select '第三阶段' ,40000 ,50000 ,'12%', 2 union all
select '第四阶段' ,50000 ,1000000 ,'15%', 2 select a.EmployeeId,a.EmployeeName,a.profit,a.floor,
sum(case when profit>=max_N then 提成阶段*cast(replace(ret,'%','') as int)/100.0
when profit>=min_N and profit>=min_N+提成阶段 then 提成阶段*cast(replace(ret,'%','') as int)/100.0
when profit>=min_N and profit<min_N+提成阶段 then (profit-min_N)*cast(replace(ret,'%','') as int)/100.0
else 0 end) as 提成
from
(select a1.*,max_N-min_N as 提成阶段 from 人员表 a1,提成级别表 a2
where a1.EmployeeId=a2.EmployeeId and lName='第一阶段') a,提成级别表 b
where a.EmployeeId=b.EmployeeId
group by a.EmployeeId,a.EmployeeName,a.profit,a.floor/*
EmployeeId EmployeeName profit floor 提成
----------- ------------ ----------- ----------- ----------------------------------------
1 wangjian 100000 30000 7500.000000
2 duyalin 80000 20000 4500.000000(所影响的行数为 2 行)
*/