有一日期型字段和其他字段,要求将每天的最后一条记录取出来,最好是用一条SELECT语句,多谢了,例:
date A 06-04-05 15:00:00 20
06-04-05 18:00:00 20
06-04-06 13:00:00 20
06-04-06 17:00:00 30
...........数据包括全年的,要求返回如下结果集06-04-05 18:00:00 20
06-04-06 17:00:00 30.....
date A 06-04-05 15:00:00 20
06-04-05 18:00:00 20
06-04-06 13:00:00 20
06-04-06 17:00:00 30
...........数据包括全年的,要求返回如下结果集06-04-05 18:00:00 20
06-04-06 17:00:00 30.....
(select max(right(date1,8)) from tbl where left(date1,8)=left(a.date1,8))
insert @a select '06-04-05 15:00:00',' 20'
union all select '06-04-05 18:00:00',' 20'
union all select '06-04-06 13:00:00 ',' 20'
union all select '06-04-06 17:00:00 ',' 30'
select * from @a
select max(date) as date,max(a) as a
from @a
group by convert(char(10),date,112)
where not exists
(select 1 from 表 where a=a.a and date<a.date)
insert @T
select '06-04-05 15:00:00', 20 union all
select '06-04-05 18:00:00', 20 union all
select '06-04-06 13:00:00', 20 union all
select '06-04-06 17:00:00', 30select * from @t g
where not exists(select 1 from @t where left([date],8)=left(g.[date],8)
and [date]>g.[date])
group by convert(char(8),date,112))
select * from 表
where date in (select date from
(select convert(char(10),date,120),max(date) as date from 表
group by convert(char(10,date,120)) A)
where not exists
(select * from 表 where convert(char(10),date,120)=convert(char(10),a.date,120) and date<a.date)
FROM table1
WHERE DATE IN (
SELECT MAX(DATE)
FROM table1
GROUP BY convert(nvarchar(20),DATE,112))
select *
from table
where date in
(
select max(date)
from table
group by convert(varchar(10),date,120))