求一SQL语句 最大值 最小值

[waslee]

我的数据库中有 A表 求一天的最大值 最小值 平均值

原表A
ID TIME aa bb cc dd ee
1 2007-1-1 12:00 30 12 15 18 19
2 2007-1-1 13:00 33 42 56 43 19
3 2007-1-1 14:00 34 12 23 18 54
4 2007-1-1 15:00 34 56 15 65 19
5 2007-1-2 12:00 30 12 15 18 67
6 2007-1-2 13:00 13 23 16 23 12
7 2007-1-2 14:00 30 12 34 12 67
8 2007-1-3 12:00 15 45 15 65 12
9 2007-1-3 13:00 16 56 65 12 19
10 2007-1-3 14:00 14 23 15 65 34
11 2007-1-4 12:00 12 15 56 12 65
12 2007-1-4 13:00 25 25 23 76 12
13 2007-1-4 14:00 56 72 78 12 45

要求结果为
ID TIME aa（max）aa（min）aa(avg) bb（max）bb（min）bb(avg) cc（max）cc（min）cc(avg) dd（max）dd（min）dd(avg)
1 2007-1-1
2 2007-1-2
3 2007-1-3
4 2007-1-4
5 2007-1-5
6 2007-1-6

[-狙击手-]

select min(id) as id ,convert(char(10),time,120) as [time] ,max(aa) as Max_aa,
min(aa) as min_aa,avg(aa) as avg_aa ,........
from a
group by convert(char(10),time,120)

[-狙击手-]

...... 自己按规则 写完吧，

[waslee]

数据库是ACCESS的
帮忙帮SQL语句补充完整。 小第是初学者。

[waslee]

happyflystone 你理解错我的意思了

上面的表A
id time
1 2007-1-1 12:00
2 2007-1-1 13:00
3 2007-1-1 14:00
4 2007-1-1 15:00
5 2007-1-1 16:00
.
.
.

要不时间或总成一天的 最大值 最小值 平均值
结果 如下
id time 最大 最小 平均
1 2007-1-1
2 2007-1-2
3 2007-1-3
4 2007-1-4
5 2007-1-5

[waslee]

大哥们帮忙了！

[waslee]

HELP ME

[中国风]

这一贴上有楼主要的答案
http://topic.csdn.net/u/20080108/20/cf7ff72b-740b-4bdc-99a4-156eca07c4a9.html

[-狙击手-]

declare @t table(ID int,[TIME] datetime,aa int,bb int,cc int,dd int,ee int)
insert @t select 
1,'2007-1-1 12:00',30,12,15,18,19 union select
2,'2007-1-1 13:00',33,42,56,43,19  union select
3,'2007-1-1 14:00',34,12,23,18,54  union select
4,'2007-1-1 15:00',34,56,15,65,19  union select
5,'2007-1-2 12:00',30,12,15,18,67  union select
6,'2007-1-2 13:00',13,23,16,23,12  union select
7,'2007-1-2 14:00',30,12,34,12,67  union select
8,'2007-1-3 12:00',15,45,15,65,12  union select
9,'2007-1-3 13:00',16,56,65,12,19  union select
10,'2007-1-3 14:00',14,23,15,65,34  union select
11,'2007-1-4 12:00',12,15,56,12,65  union select
12,'2007-1-4 13:00',25,25,23,76,12  union select
13,'2007-1-4 14:00',56,72,78,12,45 


select min(id) as id ,convert(char(10),time,120) as [time] ,
max(aa) as Max_aa,min(aa) as min_aa,avg(aa) as avg_aa ,
max(bb) as Max_bb,min(bb) as min_bb,avg(bb) as avg_bb ,
max(cc) as Max_cc,min(cc) as min_cc,avg(cc) as avg_cc ,
max(dd) as Max_dd,min(dd) as min_dd,avg(dd) as avg_dd ,
max(ee) as Max_ee,min(ee) as min_ee,avg(ee) as avg_ee 
from @t
group by convert(char(10),time,120)
/*
id          time       Max_aa      min_aa      avg_aa      Max_bb      min_bb      avg_bb      Max_cc      min_cc      avg_cc      Max_dd      min_dd      avg_dd      Max_ee      min_ee      avg_ee      
----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           2007-01-01 34          30          32          56          12          30          56          15          27          65          18          36          54          19          27
5           2007-01-02 30          13          24          23          12          15          34          15          21          23          12          17          67          12          48
8           2007-01-03 16          14          15          56          23          41          65          15          31          65          12          47          34          12          21
11          2007-01-04 56          12          31          72          15          37          78          23          52          76          12          33          65          12          40

（所影响的行数为 4 行）

*/

我没理解错吧

楼主不是这样吗？

[中国风]

SELECT Format([time],"yyyy-m-d")  AS 日期,
max(aa),min(aa),avg(aa),max(bb),min(bb),avg(bb),max(cc),min(cc),avg(cc)

FROM Tmp

group by Format([time],"yyyy-m-d") --取天数

[中国风]

楼上的搞错了。。。
楼主的是Access数据库

[cxmcxm]

select convert(varchar(10),time,120) time,max(aa) maxaa,min(aa) minaa,avg(aa) avgaa,
max(bb) maxbb,min(bb) minbb,avg(bb) avgbb,
max(cc) maxcc,min(cc) mincc,avg(cc) avgcc,
max(dd) maxdd,min(dd) mindd,avg(dd) avgdd,
max(ee) maxee,min(ee) minee,avg(ee) avgee
from 表 group by convert(varchar(10),time,120) order by 1