用sql语句按周、按月、按季、按年统

最新推荐文章于 2023-10-18 17:11:25 发布

转载最新推荐文章于 2023-10-18 17:11:25 发布 · 134 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/niaowo/p/4008683.html

文章标签：

#数据库

本文提供了按周、月、季、年进行收入统计的SQL查询语句，分别针对MySQL和SQL Server 2005数据库环境，并包括了按日统计的方法。

原文地址：http://hi.baidu.com/%BD%F0%D3%F1kl_y/blog/item/1c368ffba9388476024f5645.html

--按mySql语法统计按周,月,季,年。income为合计的价格字段，createDate为交易时间。
select sum(income)as revenue,week(createDate) as week,month(createDate) as month,
quarter(createDate)as quarter,year(createDate)as year from employee
where year(createDate) >= '2006' group by
week(createDate),month(createDate),quarter(createDate),year(createDate)
 
--sqlserver2005语法统计按周,月,季,年。
--按日
--select sum(price),day([date]) from table_name where year([date]) =
'2006' group by day([date])
--按周quarter
select sum(price),datename(week,price_time) from ble_name where
year(price_time) = '2008' group by datename(week,price_time)
--按月
select sum(price),month(price_time) from ble_name where year(price_time)
= '2008' group by month(price_time)
--按季
select sum(price),datename(quarter,price_time) from ble_name where
year(price_time) = '2008' group by datename(quarter,price_time)
--按年
select sum(price),year(price_time) from ble_name where
year(price_time) >= '2008' group by year(price_time)