查询一月中每天的最大值,该如何处理

查询一月中每天的最大值
表数据类似如下：

datetime num
-------------------------------------------------
2012-02-01 20:15:00 000 53
2012-02-01 21:15:00 000 64
2012-02-01 22:15:00 000 34
2012-02-01 23:15:00 000 65
.....
.....
2012-02-21 18:15:00 000 34
2012-02-21 19:15:00 000 123
2012-02-21 20:15:00 000 43
2012-02-21 21:15:00 000 64
2012-02-21 22:15:00 000 21
2012-02-21 23:15:00 000 31
..


计算出一个月内每天的最大值
谢谢
 

[解决办法]
/*
2012-02-01 20:15:00 000 53
2012-02-01 21:15:00 000 64
2012-03-01 22:15:00 000 34
2012-03-01 23:15:00 000 65
.....
.....
2012-04-22 18:15:00 000 34
2012-04-22 19:15:00 000 123
2012-04-22 20:15:00 000 43
2012-05-21 21:15:00 000 64
2012-05-21 22:15:00 000 21
2012-05-21 23:15:00 000 31
*/
go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
[date] datetime,
value int
)
go
insert tbl
select '2012-02-01 20:15:00',53 union all
select '2012-02-01 21:15:00',64 union all
select '2012-03-01 22:15:00',34 union all
select '2012-03-01 23:15:00',65 union all
select '2012-04-22 18:15:00',34 union all
select '2012-04-22 19:15:00',123 union all
select '2012-04-22 20:15:00',43 union all
select '2012-05-21 21:15:00',64 union all
select '2012-05-21 22:15:00',21 union all
select '2012-05-21 23:15:00',31

;with T
as
(
select *,
row_number()over(partition by datepart(mm,[date]),datepart(dd,[date]) 
order by value desc) as num from tbl
)

select [date],value from T where num=1

/*
datevalue
2012-02-01 21:15:00.00064
2012-03-01 23:15:00.00065
2012-04-22 19:15:00.000123
2012-05-21 21:15:00.00064
*/

我修改了你给的数据
[解决办法]

SQL code

declare @T table ([datetime] datetime,num int)insert into @Tselect '2012-02-01 20:15:00.000',53 union allselect '2012-02-01 21:15:00.000',64 union allselect '2012-02-01 22:15:00.000',34 union allselect '2012-02-01 23:15:00.000',65 union allselect '2012-02-21 18:15:00.000',34 union allselect '2012-02-21 19:15:00.000',123 union allselect '2012-02-21 20:15:00.000',43 union allselect '2012-02-21 21:15:00.000',64 union allselect '2012-02-21 22:15:00.000',21 union allselect '2012-02-21 23:15:00.000',31select     convert(varchar(10),[datetime],120) as [datetime],max(num) as numfrom @T group by convert(varchar(10),[datetime],120)/*datetime   num---------- -----------2012-02-01 652012-02-21 123*/
[解决办法]
上面的写错了！

SQL code
select convert(varchar(10),日期,120), 值from 表group by convert(varchar(10),日期,120), 值having 值 = MAX(值)
[解决办法]
SQL code
create  table  tbtime (dt  varchar(100),num int)goinsert tbtimeselect '2012-02-01 20:15:00',53 union allselect '2012-02-01 21:15:00',64 union allselect '2012-03-01 22:15:00',34 union allselect '2012-03-01 23:15:00',65 union allselect '2012-04-22 18:15:00',34 union allselect '2012-04-22 19:15:00',123 union allselect '2012-04-22 20:15:00',43 union allselect '2012-05-21 21:15:00',64 union allselect '2012-05-21 22:15:00',21 union allselect '2012-05-21 23:15:00',31 select  max(num)as 最大值,substring(dt,7,1) as 月份 from tbtime group by substring(dt,7,1)         drop table tbtime