sql 日周月统计和

sql本日\本周\本月统计 SQL的本月统计和本周统计 本月统计(MySQL) select * from booking where month(booking_time) = month(curdate()) and year(booking_time) = year(curdate()) 本周统计(MySQL) select * from spf_booking where month(booking_time) = month(curdate()) and week(booking_time) = week(curdate()) [SQLServer] 表名为：tableName 时间字段名为：theDate 查询本月的记录 select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE()) 查询本周的记录 select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE()) 查询本季的记录 select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE()) 其中:GETDATE()是获得系统时间的函数。 如: 表:consume_record 字段:consume(money类型) date(datetime类型) 请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量. 如:1月1200元 2月3400元 3月2800元 --按日 selectsum(consume),day([date])fromconsume_recordwhereyear([date])='2006'groupbyday([date]) --按周quarter selectsum(consume),datename(week,[date])fromconsume_recordwhereyear([date])='2006'groupbydatename(week,[date]) --按月 selectsum(consume),month([date])fromconsume_recordwhereyear([date])='2006'groupbymonth([date]) --按季 selectsum(consume),datename(quarter,[date])fromconsume_recordwhereyear([date])='2006'groupbydatename(quarter,[date]) --指定日期你就看上面的例子变通下呀，无非就是一个聚合函数和Groupby select[date],sum(consume)fromconsume_recordwhere[date]between'2006-06-01'and'2006-07-10'groupby[date] 统计博客聚合用户点击次数 SELECT author, SUM(hits) AS hits FROM infos GROUP BY author ORDER BY hits DESC 统计昨天的记录 SELECT * FROM infos WHERE (DATEDIFF(d, pubdate, GETDATE()) = 1) 统计本周的记录 SELECT * FROM infos WHERE (DATEPART(yy, pubdate) = DATEPART(yy, GETDATE())) AND (DATEPART(week, pubdate - 1) = DATEPART(week, GETDATE())) 统计本月的记录: SELECT * FROM infos WHERE (DATEPART(yy, pubdate) = DATEPART(yy, GETDATE())) AND (DATEPART([month], pubdate - 1) = DATEPART([month], GETDATE()))