select
convert(varchar(10),[date],120) as [date],
avg(data) as data
from tb
group by convert(varchar(10),[date],120)
convert(varchar(10),[date],120) as [date],
avg(data) as data
from tb
group by convert(varchar(10),[date],120)
drop table tb
go
create table tb([date] datetime,[dat] int,[name] varchar(10))
insert tb select '2007-12-01 01:10',13,'a'
insert tb select '2007-12-01 02:10',12,'a'
insert tb select '2007-12-01 03:10',16,'a'
insert tb select '2007-12-01 04:10',14,'a'
insert tb select '2007-12-01 05:10',11,'a'
insert tb select '2007-12-01 06:10',12,'a'
insert tb select '2007-12-01 07:10',18,'a'
insert tb select '2007-12-01 08:10',14,'a'
insert tb select '2007-12-01 09:10',16,'a'
insert tb select '2007-12-01 10:10',11,'a'
insert tb select '2007-12-01 11:10',15,'a'
insert tb select '2007-12-01 12:10',17,'a'
insert tb select '2007-12-01 13:10',19,'a'
insert tb select '2007-12-01 14:10',15,'a'
insert tb select '2007-12-01 15:10',11,'a'
insert tb select '2007-12-01 16:10',16,'a'
insert tb select '2007-12-01 17:10',16,'a'
insert tb select '2007-12-01 18:10',14,'a'
insert tb select '2007-12-01 19:10',18,'a'
insert tb select '2007-12-01 20:10',16,'a'
insert tb select '2007-12-01 21:10',17,'a'
insert tb select '2007-12-01 22:10',11,'a'
insert tb select '2007-12-01 23:10',16,'a'
insert tb select '2007-12-01 00:10',14,'a'
insert tb select '2007-12-02 01:10',11,'a'
insert tb select '2007-12-02 02:10',17,'a'
insert tb select '2007-12-02 03:10',13,'a'
insert tb select '2007-12-02 04:10',19,'a'
insert tb select '2007-12-02 05:10',17,'a'
insert tb select '2007-12-02 06:10',16,'a'
insert tb select '2007-12-02 07:10',11,'a'
insert tb select '2007-12-02 08:10',12,'a'
insert tb select '2007-12-02 09:10',17,'a'
insert tb select '2007-12-02 10:10',19,'a'
insert tb select '2007-12-02 11:10',16,'a'
insert tb select '2007-12-02 12:10',14,'a'
insert tb select '2007-12-02 13:10',15,'a'
insert tb select '2007-12-02 14:10',11,'a'
insert tb select '2007-12-02 15:10',19,'a'
insert tb select '2007-12-02 16:10',12,'a'
insert tb select '2007-12-02 17:10',10,'a'
insert tb select '2007-12-02 18:10',17,'a'
insert tb select '2007-12-02 19:10',16,'a'
insert tb select '2007-12-02 20:10',14,'a'
insert tb select '2007-12-02 21:10',18,'a'
insert tb select '2007-12-02 22:10',11,'a'
insert tb select '2007-12-02 23:10',16,'a'
insert tb select '2007-12-02 00:10',17,'a'
go
select
convert(varchar,[date],23) [date],
avg(dat) avgdat
from tb
group by convert(varchar,[date],23)
/*
date avgdat
------------------------------ -----------
2007-12-01 14
2007-12-02 14
*/
date DATETIME ,
data int,
NAME CHAR(2))
INSERT INTO @tab
SELECT '2007-12-01 01:10', 13 , 'a' UNION ALL
SELECT '2007-12-01 02:10', 12 , 'a' UNION ALL
SELECT '2007-12-01 03:10', 16 , 'a ' SELECT NAME,CONVERT(CHAR(10),date) date,MAX(agv) AvgValue FROM (
SELECT NAME ,date,AVG(1. *data) OVER (PARTITION BY CONVERT(CHAR(10),date)) AGV FROM @tab) t
GROUP BY NAME,CONVERT(CHAR(10),date)/*
NAME date AvgValue
---- ---------- ---------------------------------------
a 12 1 2007 13.666666
*/
之前要数据转换一下才好
我想要的是一个对象,也就是说
类 A中有三个属性,
Date date;
double data;
String name;我想按天求data的平均值,然后返回一个A的实例比如a,a.date为某一天,a.data为这一天的平均值,a.name为name
select
datename(dd,[date]),
avg(dat) avgdat
from tb
group by datename(dd,[date])
select
convert(varchar,[date],23) [date],
cast(avg(dat*1.0) as dec(9,2)) avgdat,
name
from tb
group by convert(varchar,[date],23),name
/*
date avgdat name
------------------------------ --------------------------------------- ----------
2007-12-01 14.67 a
2007-12-02 14.92 a
*/