求一条高效语句得最大值对应的日期

[好记忆不如烂笔头abc]

一条SQL语句得出
表t
如下列：id为自增1主键
id,total,time
1,10,2011-1-3 12:10:01
2,11,2011-1-3 21:04:21
3,15,2011-1-3 22:10:02
4,12,2011-1-4 11:04:21
5,11,2011-1-4 20:10:09
6,13,2011-1-4 21:05:21
7,9,2011-1-4 11:05:21
....
求每天最大值及对应的时间
得到结果如下：
maxtotal,time
15,2011-1-3 22:10:02
13,2011-1-4 21:05:21

解决立即给分，多谢！

[百年树人]

select total as maxtotal,time
from t a
where not exists(select 1 from t where datediff(dd,[time],a.[time])=0 and total>a.total)

[AcHerat]

select total as maxtotal,time
from t a
where not exists(select 1 from t where datediff(dd,[time],a.[time]) = 0 and total > a.total)

+1

支持树哥！

[--小F--]

select
  total as maxtotal,time
from
  tb t
where
  total=(select max(total) from tb where datediff(dd,[time],t.[time]) = 0) 

[chuanzhang5687]

select * from a where 
time in (select max(time) from a group by convert(varchar(10),time,120))

[chuanzhang5687]

----------------------------------
3	15	2011-01-03 22:10:02.000
6	13	2011-01-04 21:05:21.000

[好记忆不如烂笔头abc]

刚刚发现还漏了一个要求条件：
就是同一天，可能好多total值是相同的，只取第一次出现的time

[快溜]

[Quote=引用 6 楼 jycjyc 的回复:]
刚刚发现还漏了一个要求条件：
就是同一天，可能好多total值是相同的，只取第一次出现的time
[/Quote]

select total as maxtotal,time
from t a
where not exists(select 1 from t where datediff(dd,[time],a.[time]) = 0 
     and total > a.total and [time]<a.[time])

[liangyong1107]

declare @table1 table
( 
id int identity(1,1) not null,
total float null,
time datetime null
)
insert into @table1
select 10,'2011-1-3 12:10:01' union all
select 11,'2011-1-3 21:04:01' union all
select 15,'2011-1-3 22:10:01' union all
select 12,'2011-1-4 11:04:01' union all
select 11,'2011-1-4 20:10:01' union all
select 13,'2011-1-4 21:05:01' union all
select 9,'2011-1-4 11:05:01'

;with cte as
(
 select *,ROW_NUMBER() OVER(PARTITION BY Convert(Date,TIME) ORDER BY total desc) as RN from @table1
)
select * from cte where RN=1

[好记忆不如烂笔头abc]

to ssp2009:
您的这个会每天出来很多呀，并不是每天只显示1条

[好记忆不如烂笔头abc]

[Quote=引用 8 楼 liangyong1107 的回复:]
SQL code


declare @table1 table
(
id int identity(1,1) not null,
total float null,
time datetime null
)
insert into @table1
select 10,'2011-1-3 12:10:01' union all
select 11,'2011-1-3 21:……
[/Quote]
强！

[AcHerat]

declare @table1 table
( 
id int identity(1,1) not null,
total float null,
time datetime null
)
insert into @table1
select 11,'2011-1-3 12:10:01' union all
select 15,'2011-1-3 21:04:01' union all
select 15,'2011-1-3 22:10:01' union all
select 12,'2011-1-4 11:04:01' union all
select 11,'2011-1-4 20:10:01' union all
select 13,'2011-1-4 21:05:01' union all
select 13,'2011-1-4 11:05:01'

select total as maxtotal,time
from @table1 a
where not exists(select 1 from @table1 where datediff(dd,[time],a.[time]) = 0 
			and (total > a.total or (total = a.total and id < a.id)))

/**************

maxtotal               time
---------------------- -----------------------
15                     2011-01-03 21:04:01.000
13                     2011-01-04 21:05:01.000

(2 行受影响)