如何求工资异动报表
年月, 工号,工资
200601, 001,900
200602, 001,900
200603, 001,900
200604, 001,900
200605, 001,1000
200606, 001,1000
200607, 001,900
需求结果
200601, 001,900
200605, 001,1000
200607, 001,900
注意----如果工资不断提薪,或降薪不等同以前某月的工资则可用方法:select min(年月) as 年月,工号,工资 from 表 group by 工号,工资
但问题是降薪确实出现降回以前的.如200604与200607都是900元
请指教.
谢谢!!!!
[解决办法]
--創建測試環境
Create Table TEST
(年月 Char(6),
工号 Char(3),
工资 Int)
--插入數據
Insert TEST Select '200601 ', '001 ',900
Union All Select '200602 ', '001 ',900
Union All Select '200603 ', '001 ',900
Union All Select '200604 ', '001 ',900
Union All Select '200605 ', '001 ',1000
Union All Select '200606 ', '001 ',1000
Union All Select '200607 ', '001 ',900
GO
--測試
Select * From TEST A
Where Not Exists(Select 工资 From TEST Where DateDiff(mm, 年月 + '01 ', A.年月 + '01 ') = 1 And 工资 = A.工资)
GO
--刪除測試環境
Drop Table TEST
--結果
/*
年月工号工资
200601001900
2006050011000
200607001900
*/
[解决办法]
create table #temp_gz (
年月 varchar(6),
工号 varchar(3),
工资 float )
insert into #temp_gz
select '200601 ', '001 ',900 union all
select '200602 ', '001 ',900 union all
select '200603 ', '001 ',900 union all
select '200604 ', '001 ',900 union all
select '200605 ', '001 ',1000 union all
select '200606 ', '001 ',1000 union all
select '200607 ', '001 ',900
select a.年月,a.工号,a.工资 from
( select row_number() over (order by 年月 ) as id ,* from #temp_gz ) a left join
( select row_number() over (order by 年月 ) as id ,* from #temp_gz ) b
on a.id = b.id + 1
where abs(isnull(a.工资,0)-isnull(b.工资,0)) > 0.005
结果
--------------------------------
200601001900
2006050011000
200607001900
我的异常网推荐解决方案：软件开发者薪资,http://www.myexception.cn/other/1391128.html
