一定要在Access中实现哦
通过调薪记录列出员工各月基本工资,要求每月以最后一次调薪记录为当月基本工资:
调薪记录表txls结构如下:
date empno salary
-------------------------------
2005-01-02 P001 2000.00
2005-01-10 P001 2500.00
2005-02-11 P001 2800.00
2005-02-25 P001 3000.00
2005-01-10 P002 2800.00我要得出这样的结果:
Year Month empno salary
-------------------------
2005 01 P001 2500.00
2005 02 P001 3000.00
2005 01 P002 2800.00
通过调薪记录列出员工各月基本工资,要求每月以最后一次调薪记录为当月基本工资:
调薪记录表txls结构如下:
date empno salary
-------------------------------
2005-01-02 P001 2000.00
2005-01-10 P001 2500.00
2005-02-11 P001 2800.00
2005-02-25 P001 3000.00
2005-01-10 P002 2800.00我要得出这样的结果:
Year Month empno salary
-------------------------
2005 01 P001 2500.00
2005 02 P001 3000.00
2005 01 P002 2800.00
from
(select year([date]) as [year],month([date]) as [Month],max(day(date)) as d,empno,salary from t group by year([date]),empno,month([date]),salary) as temp
group by temp.year,empno,temp.month~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~测试结果:
Year Month Empno Salary2005 01 P001 2500
2005 02 P001 3000
2005 01 P002 2800
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select temp.Year,temp.Month,Empno,max(salary) as Salary
from
(select year([date]) as [year],month([date]) as [Month],max(day(date)) as d,empno,salary from TXLS group by year([date]),empno,month([date]),salary) as temp
group by temp.year,empno,temp.month~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~测试结果:
Year Month Empno Salary2005 01 P001 2500
2005 02 P001 3000
2005 01 P002 2800