如:
表: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]
--2011年每月注册的人数 SELECT CONVERT(CHAR(7), joindate,120), COUNT(*) FROM member where year(joindate)=2011 GROUP BY CONVERT(CHAR(7), joindate,120) --2011年3月每天注册的人数 SELECT CONVERT(CHAR(10), joindate,120), COUNT(*) FROM member where year(joindate)=2011 and month(joindate)=3 GROUP BY CONVERT(CHAR(10), joindate,120)
--获取每家公司前一百条新闻的ID select a.WebNewsID from WebNews as a where a.WebNewsID in (select top 100 WebNewsID from WebNews where Code=a.Code order by PageGetTime desc) group by a.WebNewsID,a.Code order by a.Code