查询最近数据

[Gere Yeung]

很多数据是随时间变化的，如工资：

ID EmployeeID Date salary

1 001 2009-1-1 10000.00

2 002 2009-1-1 11000.00

3 003 2009-1-1 12000.00

4 001 2010-1-1 15000.00

5 002 2010-2-1 16000.00

6 003 2010-3-1 17000.00

需要查询截止日期的工资状态，怎么查询？

[ACMAIN_CHM]

select *
from table1 a
where not exists (select 1 from table1 where EmployeeID=a.EmployeeID and Date>a.Date)

[ACMAIN_CHM]

参考下贴中的其它方法。

http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
分组取最大N条记录方法征集

[wwwwb]

贴你要求结果出来看看
select top 1 * from tt where [Date]<date() order by [date] desc
or
select a.* frm tt a inner join
(select EmployeeID,max(Date) as ma from tt group by EmployeeID) b
on a.EmployeeID=b.EmployeeID and a.[date]=b.ma

[wwwwb]

or
select * from tt a where [date] in(select top 1 [date] from tt
where a.EmployeeID=b.EmployeeID order by [date] desc)