一条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解决立即给分,多谢!
表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解决立即给分,多谢!
from t a
where not exists(select 1 from t where datediff(dd,[time],a.[time])=0 and total>a.total)
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支持树哥!
total as maxtotal,time
from
tb t
where
total=(select max(total) from tb where datediff(dd,[time],t.[time]) = 0)
time in (select max(time) from a group by convert(varchar(10),time,120))
就是同一天,可能好多total值是相同的,只取第一次出现的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])
(
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
您的这个会每天出来很多呀,并不是每天只显示1条
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 行受影响)