根据用户收入 Income 返回他所要交的税
最好用存储过程实现，下面只是个模拟，不一定与现实对应。
级别 起征点 超过 不超过 税率
rank baseIncome overmin overmax taxs
1 2000 0 1000 5%
2 2000 1000 3000 10%
3 2000 3000 6000 15%
4 2000 6000 10000 20%
5 2000 10000 15000 25%
如上表，程序最好写活，就是说 baseIncome overmin overmax taxs 在改变的情况下程序不用修改
谢谢，那技术给我半个小时。没搞定。在这里请教高人
------解决方案--------------------
case when即可.
------解决方案--------------------
建个税率表
查表计算就可以
------解决方案--------------------
between and .>
------解决方案--------------------
- SQL code
create table tb(rank int identity(1,1),baseIncome int,overmin int,overmax int,taxs varchar(10))insert into tb select 2000,0,1000,'5%'insert into tb select 2000,1000,3000,'10%'insert into tb select 2000,3000,6000,'15%'insert into tb select 2000,6000,10000,'20%'insert into tb select 2000,10000,15000,'25%'create proc wsp@Income moneyasselect 所要交的税=(@Income-baseIncome)*cast(replace(taxs,'%','') as numeric(5,2))/100from tbwhere @Income-baseIncome between overmin and overmax-1 exec wsp 5000--结果：450
------解决方案--------------------
- SQL code
create table cTaxRate(minV decimal(18,2), maxV decimal(18,2),TaxRate decimal(18,2), --税率Allowance decimal(18,2) --速算扣除数)create function cFunctionTax(@Amount decimal(18,2))returns decimal(18,2)asBegin declare @taxV decimal(18,2), @taxBase decimal(18,2) select @taxBase=2000-- select @taxBase=字段 from 表 select @taxV=(@[email protected])*TaxRate-Allowance from cTaxRate where (@[email protected])>=minV and (@[email protected])<maxV return @TaxVEnd
------解决方案--------------------
- SQL code
create table 税率表 (rank int ,baseIncome numeric(18,2) , overmin numeric(18,2),overmax numeric(18,2) , taxs numeric(18,6))insert 税率表 select 1, 2000, 0 , 1000 , 0.05union all select 2, 2000, 1000 , 3000 , 0.1union all select 3, 2000, 3000 , 6000 , 0.15union all select 4, 2000, 6000 , 10000 , 0.2union all select 5, 2000, 10000, 15000 , 0.25gocreate proc pr_计算税金@income numeric(18,2)asselect sum(case when @income>baseIncome+overmax then overmax-overmin else @income-baseIncome-overmin end *taxs) as 税金from 税率表where baseIncome+overmin<[email protected]goexec pr_计算税金 2500--25exec pr_计算税金 3500--100exec pr_计算税金 9500--1000exec pr_计算税金 12000--1500
------解决方案--------------------
- SQL code
create table #tab (id int identity(1,1),baseincome int,overmin int,overmax int,taxs float)insert into #tab values(2000,0,1000,0.05)insert into #tab values(2000,1000,3000,0.1)insert into #tab values(2000,3000,6000,0.15)insert into #tab values(2000,6000,10000,0.2)insert into #tab values(2000,10000,15000,0.25)create proc #tab_p (@money float)asbegin select sum(case when @money-(baseincome+overmin)>overmax then overmax when @money-(baseincome+overmin)<0 then 0 else @money-(baseincome+overmin) end*taxs)from #tab endexec #tab_p 4500200