高校问答搜索功能怎么测试:向高手请假sql 统计问题
来源：百度文库 编辑：高校问答 时间：2024/05/07 04:40:55
如:
表:consume_record
字段:consume (money类型)
date (datetime类型)
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元
...
一楼的谢谢你!你的按日是一个月的第几天吗?
如果给一个时间范围,求出这段时间内各天的统计怎么写.如从2006-06-01到2006-07-10中各天的统计.谢谢.
二楼的,sql server 有to_char()这个函数吗?我这边显示没有.
--按日
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]
--BTW to_char是Oracle裏面的转换函数。
select sum(consume) from consume_record where to_char(date,\'yyyy-mm-dd\') >= \'2006-06-10\'and to_char(date,\'yyyy-mm-dd\') <= \'2006-07-10\'