select t.* from tb t where dt = ( select top 1 dt from tb where datediff(hh,dt,t.dt) = 0 )
create table #temp ( id int identity, [time] datetime ) insert into #temp select '2011-01-01 00:01:11' union all select '2011-01-01 10:01:11' union all select '2011-01-01 10:01:11' union all select '2011-01-01 20:01:11' union all select '2011-01-01 20:01:11'select * from #tempselect b.* from (select distinct [time] = convert(varchar(13), [time], 120) from #temp) a cross apply (select top(1) * from #temp where convert(varchar(13), [time], 120) = a.[time]) b
declare @table table (id int,col datetime) insert into @table select 1,'2010-12-31 9:00:00' union all select 2,'2010-12-30 9:00:00' union all select 3,'2010-12-23 8:00:00' union all select 4,'2010-12-24 8:00:00' union all select 5,'2010-12-23 7:00:00'SELECT MAX(id) AS id,MAX(col) AS col FROM @table GROUP BY DATEPART(hh,col) /* id col ----------- ----------------------- 5 2010-12-23 07:00:00.000 4 2010-12-24 08:00:00.000 2 2010-12-31 09:00:00.000 */
貌似不管用吧,我放在sql server 2008 里,半天没出一条数据
declare @table table (id int,col datetime) insert into @table select 1,'2010-12-31 9:00:00' union all select 2,'2010-12-30 9:00:00' union all select 3,'2010-12-23 8:00:00' union all select 4,'2010-12-24 8:00:00' union all select 5,'2010-12-23 7:00:00'SELECT MAX(id) AS id,MAX(col) AS col FROM @table GROUP BY convert(varchar(13),col,120)+'-'+Rtrim(DATEPART(hh,col))
(
id int identity,
[time] datetime
)
insert into #temp
select '2011-01-01 00:01:11' union all
select '2011-01-01 10:01:11' union all
select '2011-01-01 10:01:11' union all
select '2011-01-01 20:01:11' union all
select '2011-01-01 20:01:11'select * from #tempselect b.* from
(select distinct [time] = convert(varchar(13), [time], 120) from #temp) a
cross apply
(select top(1) * from #temp where convert(varchar(13), [time], 120) = a.[time]) b
declare @table table (id int,col datetime)
insert into @table
select 1,'2010-12-31 9:00:00' union all
select 2,'2010-12-30 9:00:00' union all
select 3,'2010-12-23 8:00:00' union all
select 4,'2010-12-24 8:00:00' union all
select 5,'2010-12-23 7:00:00'SELECT MAX(id) AS id,MAX(col) AS col FROM @table
GROUP BY DATEPART(hh,col)
/*
id col
----------- -----------------------
5 2010-12-23 07:00:00.000
4 2010-12-24 08:00:00.000
2 2010-12-31 09:00:00.000
*/
insert into @table
select 1,'2010-12-31 9:00:00' union all
select 2,'2010-12-30 9:00:00' union all
select 3,'2010-12-23 8:00:00' union all
select 4,'2010-12-24 8:00:00' union all
select 5,'2010-12-23 7:00:00'SELECT MAX(id) AS id,MAX(col) AS col FROM @table
GROUP BY convert(varchar(13),col,120)+'-'+Rtrim(DATEPART(hh,col))