在access中这样的SQL语句怎么写？

[gzllich]

一定要在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

[gxgyj]

把楼主的表名代换进去...t--->txls,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 Salary

2005 01 P001 2500
2005 02 P001 3000
2005 01 P002 2800

[gxgyj]

d

[见丰]

up,有点意思，一定要用sql写?

[Nobu]

d

[caiso]

顶一下

[zhangwei1437]

取每个月的最大的日期，按照人分组

[gxgyj]

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 t group by year([date]),empno,month([date]),salary) as temp
group by temp.year,empno,temp.month

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~测试结果：
Year Month Empno Salary

2005 01 P001 2500
2005 02 P001 3000
2005 01 P002 2800

[78hgdong]

同意!

[gzllich]

唉，都不行，算了还是在前台程序里面写吧