--按日

select

sum

(consume),

day

([

date

])

from

consume_record

where

year

([

date

]) =

'2006'

group

by

day

([

date

])

 

--按周quarter

select

sum

(consume),datename(week,[

date

])

from

consume_record

where

year

([

date

]) =

'2006'

group

by

datename(week,[

date

])

 

--按月

select

sum

(consume),

month

([

date

])

from

consume_record

where

year

([

date

]) =

'2006'

group

by

month

([

date

])

 

--按季

select

sum

(consume),datename(quarter,[

date

])

from

consume_record

where

year

([

date

]) =

'2006'

group

by

datename(quarter,[

date

])

 

--按年 

select

sum

(consume),

year

([

date

])

from

consume_record

where 

group

by

year

([

date

])

 

--这个按每五分钟的

[code=SQL]

create 

table 

tb(时间 datetime , 金额 

int

)

insert 

into 

tb 

values

(

'2007-1-1 10:00:23' 

,          8 )

insert 

into 

tb 

values

(

'2007-1-1 10:01:24' 

,          4 )

insert 

into 

tb 

values

(

'2007-1-1 10:05:00' 

,          2 )  

insert 

into 

tb 

values

(

'2007-1-1 10:06:12' 

,          3 )

insert 

into 

tb 

values

(

'2007-1-1 10:08:00' 

,          1 )

insert 

into 

tb 

values

(

'2007-1-1 10:12:11' 

,          5 )

go

 

--时间段>=10:00:00 and 时间段<10:05:00

select 

dateadd(mi,(datediff(mi,

convert

(

varchar

(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,

convert

(

varchar

(10),时间,120)) 

as 

时间段,

       

count

(*) 

as 

行数,

       

sum

(金额) 

as 

总金额

from 

tb

group 

by 

dateadd(mi,(datediff(mi,

convert

(

varchar

(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,

convert

(

varchar

(10),时间,120))

/*

时间段                                                    行数          总金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:00:00.000                                3           14

2007-01-01 10:05:00.000                                2           4

2007-01-01 10:10:00.000                                1           5

（所影响的行数为 3 行）

*/

 

--时间段>10:00:00 and 时间段<=10:05:00

select 

dateadd(mi,(datediff(mi,

convert

(

varchar

(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,

convert

(

varchar

(10),时间,120)) 

as 

时间段,

       

count

(*) 

as 

行数,

       

sum

(金额) 

as 

总金额

from 

tb

group 

by 

dateadd(mi,(datediff(mi,

convert

(

varchar

(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,

convert

(

varchar

(10),时间,120))

/*

时间段                                                    行数          总金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:00:00.000                                2           12

2007-01-01 10:05:00.000                                3           6

2007-01-01 10:10:00.000                                1           5

 

（所影响的行数为 3 行）

*/

 

drop 

table 

tb