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

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)

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

posted on 2019-07-24 07:48  Ding5688 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/ding5688/p/11235587.html