我的一个关于时间字段的问题(50分)

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #1

有这样一个表
DATETIME A B C
2002-11-01 01:00:00 .. .. ..
2002-11-01 02:00:00
2002-11-01 03:00:00
2002-11-01 04:00:00
2002-11-02 01:00:00
2002-11-02 02:00:00
2002-11-02 03:00:00
2002-11-02 04:00:00
2002-11-03 01:00:00
2002-11-03 02:00:00
2002-11-03 03:00:00
2002-11-03 04:00:00 .. .. ..
...
我想得到每个时区的平均值
TIME A B C
01
02
03
04 .. .. ..
应该怎么做

 

Q

qianwt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #2

select convert(char(2),[DATETIME],108) Time,avg(A) A,avg(B) B,avg(C) C
from 表名 group by Time

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #3

convert(char(2),[DATETIME],108) 什么意思，怎么用法？

 

Q

qianwt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #4

convert(char(2),[DATETIME],108) 是取DATETIME字段的小时的字符串，

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #5

我试试看

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #6

用的sql server7.0数据库，该字段为timestamp类型

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #7

好像不行啊，提示TIME字段错误

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #8

select convert(char(2),[DATETIME],108),A,B,C FROM TABLE1.DBF
这样是可以的

 

N

net.meteor

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #9

select DATEPART(DateTime) Time,avg(A) A,avg(B) B,avg(C) C
from 表名 group by DATEPART(DateTime)

 

N

net.meteor

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #10

不好意思应该是这样：
select DATEPART(Hour,DateTime) Time,avg(A) A,avg(B) B,avg(C) C
from 表名 group by DATEPART(Hour,DateTime)

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-06

• #11

接受答案了.