例如数据为:
ID GetTime
1 2006-07-01 10:45:15
2 2006-07-01 14:45:15
3 2006-07-02 10:45:15
4 2006-07-02 14:45:15
5 2006-07-03 10:00:15我要用SQL得到一个结果,结果里只保留每天最大GetTime的数据.得出的结果即为:
ID GetTime
2 2006-07-01 14:45:15
4 2006-07-02 14:45:15
5 2006-07-03 10:00:15这个SQL如何写?用的数据库为SQLServer2000.
ID GetTime
1 2006-07-01 10:45:15
2 2006-07-01 14:45:15
3 2006-07-02 10:45:15
4 2006-07-02 14:45:15
5 2006-07-03 10:00:15我要用SQL得到一个结果,结果里只保留每天最大GetTime的数据.得出的结果即为:
ID GetTime
2 2006-07-01 14:45:15
4 2006-07-02 14:45:15
5 2006-07-03 10:00:15这个SQL如何写?用的数据库为SQLServer2000.
from tab
where id in
(
select max(ID) from tab group by left(GetTime,10)--字符型就这样,日期型改一下
)估计还有更好的办法
小的话一条sql语句就够了
C.ID ID,B.GetTime
from
(select A.ID,A.ADate,Max(ATime)
from
(select
ID,convert(varchar,GetTime,23) ADate,convert(varchar,GetTime,14) ATime
from tablename) A
group by A.ID,A.Date) C left join tablename C.ID=B.ID