SQL按照日、周、月、年统计数据代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SQL按照日、周、月、年统计数据

作者: / 发布于2011/1/1/ 640

<div>表:consume_record 
字段:consume (money类型) 
date (datetime类型)</div>
<div>请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量. 
如:1月 1200元 
2月 3400元 
3月 2800元</div>
<div>--按日 
select sum(consume),day([date]) from consume_record where year([date]) = '2006' group by day([date])</div>
<div>--按周quarter 
select sum(consume),datename(week,[date]) from consume_record where year([date]) = '2006' group by datename(week,[date])</div>
<div>--按月 
select sum(consume),month([date]) from consume_record where year([date]) = '2006' group by month([date])</div>
<div>--按季 
select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = '2006' group by datename(quarter,[date])</div>
<div></div>
<div>--指定日期你就看上面的例子变通下呀，无非就是一个聚合函数和Group by</div>
<div>select [date],sum(consume) from consume_record where [date] between '2006-06-01' and '2006-07-10' group by [date]</div>
<div></div>

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label