有一表工资调整表
iniwage调前工资,lastwage调后工资,day调整日期
table(iniwage,lastwage,day)
假设天数为30天
如有数值如下
1000 1500 6
1500 2005 15
2005 2500 18
2500 3000 20现要得到工资:基本工资:1000/30*6+1500/30*9+2005/30*3+2500/30*2+3000/30*10这样sql如何实现;
iniwage调前工资,lastwage调后工资,day调整日期
table(iniwage,lastwage,day)
假设天数为30天
如有数值如下
1000 1500 6
1500 2005 15
2005 2500 18
2500 3000 20现要得到工资:基本工资:1000/30*6+1500/30*9+2005/30*3+2500/30*2+3000/30*10这样sql如何实现;
解决方案 »
- SQL自动删除6个月前的记录语句~~~~
- 求解,过滤重复数据的问题。
- 日期格式转换
- 跨网段访问数据库的问题
- 求一更新的存储过程
- 百分求助,急,up有分
- 请问,使用stored procedure 保存 一条销售记录和N条销售明细记录????
- 有用informix 的人吗
- 我想在sql里写一个存储过程,每次通过传sqlSTR语句(例如:select * from A或B或select b*,a.* from a,b),页数,返回我所要的记录,应该怎样写,谢谢!!!
- win98能做局域网的SQL SERVER数据库服务器的操作系统吗?
- 如何自动生成具有30万条记录的数据库?
- xp系统上能完全安装SQL.Server.2005.简体中文企业版吗?我的机器上现在已经全部安装了server2000
--> -->
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([iniwage] int,[lastwage] int,[day] int)
Insert #T
select 1000,1500,6 union all
select 1500,2005,15 union all
select 2005,2500,18 union all
select 2500,3000,20
Go
with C
as
(select [iniwage],[DiffDay]=[day]-(select isnull(max([day]),0) from #T where [day]<t.[day]) from #T t
union all
select max([lastwage]),10 from #T)
select cast(sum([iniwage]*1.0/30*[DiffDay]) as int)
from C(4 個資料列受到影響)-----------
2017(1 個資料列受到影響)
declare @daynum int
set @daynum=30
insert into @table
values(1000,1500,6 )
insert into @table
values(1500,2005,15 )insert into @table
values(2005,2500,18 )
insert into @table
values(2500,3000,20 )
declare @money money
declare @iniwage money,@lastwage money,@day int,@lastday int
set @lastday=0
declare temp cursor for select iniwage,lastwage,day from @table order by day asc
open temp
fetch next from temp into @iniwage,@lastwage,@day
while(@@fetch_status=0)
beginset @money=isnull(@money,0)+@iniwage/@daynum*(@day-@lastday)
set @lastday=@day
fetch next from temp into @iniwage,@lastwage,@day
end
close temp
deallocate tempset @money=isnull(@money,0)+@lastwage/@daynum*(@daynum-@lastday)select @money
--select 1000/30.00*6+1500/30.00*9+2005/30.00*3+2500/30.00*2+3000/30.00*10
declare @table table (iniwage int,lastwage int,day int)
insert into @table
select 1000,1500,6 union all
select 1500,2005,15 union all
select 2005,2500,18 union all
select 2500,3000,20
select sum([end]*1.0/30*[day]) from
(select [end]=isnull(a.lastwage,b.iniwage),[day]=isnull(b.day,30)-isnull(a.day,0)
from @table a full join @table b on a.lastwage=b.iniwage)a--结果:
2017.166663
FROM
(select iniwage/30 As WAGE ,day AS DAY from iniwage)
AS A呵呵,试试看~~~~
Create table #T([iniwage] int,[lastwage] int,[day] int)
Insert #T
select 1000,1500,6 union all
select 1500,2005,15 union all
select 2005,2500,18 union all
select 2500,3000,20
Go
with C1
as
(select *,rowid = row_number() over(order by iniwage) from #t
union all
select max(lastwage) ,0,30 - max([day]) ,199 from #t)
select sum(a.iniwage /30.0 * (a.[day] - isnull(b.[day],0)))
from c1 as a
left join c1 as b
on a.rowid = b.rowid + 1
drop table #t
/*
---------------------------------------
2017.166663(1 行受影响)
*/
INSERT @t
SELECT 1000,1500,6 UNION ALL
SELECT 1500,2005,15 UNION ALL
SELECT 2005,2500,18 UNION ALL
SELECT 2500,3000,20;WITH fc AS
(
SELECT ROW_NUMBER() OVER (ORDER BY day) idx,* FROM @t
)SELECT SUM(day * iniwage /30)
FROM
(
SELECT a.day-ISNULL(b.day,0) day,a.iniwage
FROM fc a
LEFT JOIN fc b
ON a.idx=b.idx+1
UNION ALL
SELECT 30-day,lastwage FROM fc a
WHERE NOT EXISTS
(
SELECT 1 FROM fc WHERE idx>a.idx
)
) x