select *
from tablename a join
( select [name],max([date]) as mdate
from tablename
group by [name]
) b
on a.name=b.name and a.date=b.mdate注意,最好不用姓名作为主键。因为有重名的人员的可能。
from tablename a join
( select [name],max([date]) as mdate
from tablename
group by [name]
) b
on a.name=b.name and a.date=b.mdate注意,最好不用姓名作为主键。因为有重名的人员的可能。
而是存成INT型, 如 年 月
2003 10
2003 11
之类的呢,也是要取出最大的日期,上例就是取出 2003年11月的
where cast(年 as varchar)+'-'+cast(月) as varchar)
=(select max(cast(年 as varchar)+'-'+cast(月) as varchar)) from 表 where 姓名=a.姓名)
(select 姓名,max(date) from 表a group by 姓名) t2 on t1.姓名=t2.姓名 and t1.date=t2.date
where not exists(
select 1 from 表 where 姓名=a.姓名 and cast(a.年 as varchar(5))+ cast(a.月 as varchar(5))<cast(年 as varchar(5))+ cast(月 as varchar(5)))
select * from 表a t1 inner join
(select 姓名,max(cast(年 as varchar(4))+cast(月 as varchar(2))) as date from 表a group by 姓名) t2 on t1.姓名=t2.姓名 and cast(t1.年 as varchar(4)+cast(t1.月 as varchar(2))=t2.date