SELECT * FROM TB T WHERE DATE=(SELECT MIN(DATE) FROM TB WHERE DATEDIFF(DD,DATE,T.DATE)=0)
--> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A go create table #A (Date datetime,Temperature numeric(3,1)) insert into #A select '2010-3-1 11:01:01',21.2 union all select '2010-3-1 11:02:31',22.1 union all select '2010-3-1 13:01:51',23.4 union all select '2010-3-2 22:04:01',14.1 union all select '2010-3-2 23:12:34',15.0 union all select '2010-3-4 09:30:12',20.0 union all select '2010-3-4 11:01:01',10.9 union all select '2010-3-4 06:23:16',25.8 union all select '2010-3-7 11:01:01',22.1SELECT * FROM #A T WHERE DATE=(SELECT MIN(DATE) FROM #A WHERE DATEDIFF(DD,DATE,T.DATE)=0) /* (所影响的行数为 9 行)Date Temperature ------------------------------------------------------ ----------- 2010-03-01 11:01:01.000 21.2 2010-03-02 22:04:01.000 14.1 2010-03-04 06:23:16.000 25.8 2010-03-07 11:01:01.000 22.1(所影响的行数为 4 行)
select * from tb t where not exists(select 1 from tb where datediff(dd,Date,t.Date)=0 and Temperature<t.Temperature)
select * from tb t where not exists(select 1 from tb where datediff(dd,[date],t.[date])=0 and [date]<t.[date])
--借77数据 if object_id('tempdb.dbo.#A') is not null drop table #A go create table #A (Date datetime,Temperature numeric(3,1)) insert into #A select '2010-3-1 11:01:01',21.2 union all select '2010-3-1 11:02:31',22.1 union all select '2010-3-1 13:01:51',23.4 union all select '2010-3-2 22:04:01',14.1 union all select '2010-3-2 23:12:34',15.0 union all select '2010-3-4 09:30:12',20.0 union all select '2010-3-4 11:01:01',10.9 union all select '2010-3-4 06:23:16',25.8 union all select '2010-3-7 11:01:01',22.1 select * from #A t where not exists(select 1 from #A where datediff(dd,Date,t.Date)=0 and Temperature<t.Temperature) /* Date Temperature ----------------------- --------------------------------------- 2010-03-01 11:01:01.000 21.2 2010-03-02 22:04:01.000 14.1 2010-03-04 11:01:01.000 10.9 2010-03-07 11:01:01.000 22.1(4 行受影响) */
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A (Date datetime,Temperature numeric(3,1))
insert into #A
select '2010-3-1 11:01:01',21.2 union all
select '2010-3-1 11:02:31',22.1 union all
select '2010-3-1 13:01:51',23.4 union all
select '2010-3-2 22:04:01',14.1 union all
select '2010-3-2 23:12:34',15.0 union all
select '2010-3-4 09:30:12',20.0 union all
select '2010-3-4 11:01:01',10.9 union all
select '2010-3-4 06:23:16',25.8 union all
select '2010-3-7 11:01:01',22.1SELECT * FROM #A T WHERE DATE=(SELECT MIN(DATE) FROM #A WHERE DATEDIFF(DD,DATE,T.DATE)=0)
/*
(所影响的行数为 9 行)Date Temperature
------------------------------------------------------ -----------
2010-03-01 11:01:01.000 21.2
2010-03-02 22:04:01.000 14.1
2010-03-04 06:23:16.000 25.8
2010-03-07 11:01:01.000 22.1(所影响的行数为 4 行)
from tb t
where not exists(select 1
from tb
where datediff(dd,Date,t.Date)=0 and Temperature<t.Temperature)
from tb t
where not exists(select 1 from tb where datediff(dd,[date],t.[date])=0 and [date]<t.[date])
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A (Date datetime,Temperature numeric(3,1))
insert into #A
select '2010-3-1 11:01:01',21.2 union all
select '2010-3-1 11:02:31',22.1 union all
select '2010-3-1 13:01:51',23.4 union all
select '2010-3-2 22:04:01',14.1 union all
select '2010-3-2 23:12:34',15.0 union all
select '2010-3-4 09:30:12',20.0 union all
select '2010-3-4 11:01:01',10.9 union all
select '2010-3-4 06:23:16',25.8 union all
select '2010-3-7 11:01:01',22.1
select *
from #A t
where not exists(select 1
from #A
where datediff(dd,Date,t.Date)=0 and Temperature<t.Temperature)
/*
Date Temperature
----------------------- ---------------------------------------
2010-03-01 11:01:01.000 21.2
2010-03-02 22:04:01.000 14.1
2010-03-04 11:01:01.000 10.9
2010-03-07 11:01:01.000 22.1(4 行受影响)
*/