sql语句分别按日,按周,按月,按季统计金额

最新推荐文章于 2023-08-21 20:52:03 发布

一剑平江湖

最新推荐文章于 2023-08-21 20:52:03 发布

阅读量5.1k

点赞数

分类专栏：数据库文章标签： sql date

本文链接：https://blog.csdn.net/diligentcatrich/article/details/6273360

版权

149 篇文章 1 订阅

订阅专栏

如:

表:consume_record

字段:consume (money类型)

date (datetime类型)

请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.

如:1月 1200元

2月 3400元

3月 2800元

-- 按日

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 ] )

-- 指定日期你就看上面的例子变通下呀，无非就是一个聚合函数和Group by

select [ date ] , sum (consume) from consume_record where [ date ] between ' 2006-06-01 ' and ' 2006-07-10 ' group by [ date ]

关注