sql月度分组_sqlserver中如何实现时间按月,日,小时分组查询

--按照月份统计

select count(id) cnt,datepart(mm,time) [Month]

from [table]

where [time] between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'

group by datepart(mm,time)

--按照日统计

select count(id) cnt,datepart(dd,time) [Day]

from [table]

where time between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'

group by datepart(dd,time)

--按照小时统计

select count(id) cnt,datepart(hh,time) [Hour]

from [table]

where [time] between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'

group by datepart(hh,time)

==========================================================================

1、每年

select year(ordertime) AS '年',

sum(Total) '销售合计'

from order_list

group by year(ordertime)

2、每月

select year(ordertime) '年',

month(ordertime) '月',

sum(Total) '销售合计'

from order_list

group by year(ordertime),

month(ordertime)

3、每日

select year(ordertime) '年',

month(ordertime) '月',

day(ordertime) '日',

sum(Total) '销售合计'

from order_list

group by year(ordertime),

month(ordertime),

day(ordertime)

另外每日也可以这样:

select convert(char(8),ordertime,112) dt,

sum(Total) '销售合计'

from order_list

group by convert(char(8),ordertime,112)

另外，每月(年、日)的记录条数

select year(ordertime) '年',

month(ordertime) '月',

count(*) '销售记录'

from order_list

group by year(ordertime),

month(ordertime)

==========================================================================