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

来源：互联网发布：游戏支付软件编辑：程序博客网时间：2024/05/02 05:04

如:

表: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]

分类: SQL脚本

0 0