oracle sql 按日，周，月，年统计

oracle sql 按日，周，月，年统计

http://www.cnblogs.com/gaojing/archive/2008/11/07/1328657.html   [oracle] to_date() 与 to_char() 日期和字符串转换 http://blog.sina.com.cn/s/blog_5fcdcbf70100er92.html   SQL   按月统计 http://www.cnblogs.com/wenbhappy/archive/2008/07/02/1233660.html SQL按照日、周、月、年统计数据 http://blog.19lou.com/10572276/viewspace-755729 SQL按日期归类统计数据 http://piaoling.iteye.com/blog/465703 oracle的rank,over partition涵数使用 http://www.programmersheaven.com/mb/oracle/344879/344879/invalid-indentifier-error-with-this-sql-statement/?S=B20000   ORA-00904: "DAY": invalid identifier http://www.diybl.com/course/7_databases/sql/sqlServer/2008626/128408.html   Oracle SQL group by day/week/month/quarter http://blog.csdn.net/jamex/archive/2008/06/24/2582514.aspx   Oracle的日期函数 http://stackoverflow.com/questions/482912/sql-group-by-year-month-week-day-hour-sql-vs-procedural-performance    经过实践 根据http://blog.19lou.com/10572276/viewspace-755729 SQL按日期归类统计数据 如: 表: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] 以上适用与非ORALE，因为oracle不支持 day函数 oracle中进行上述操作需要 You can choose any of the following options and replace the text string in SQLs to let the reports be as daily/weekly/monthly/Quarterly reports.. --daily to_char(rad.created_on+15/24, 'YYYY-MM-DD') --weekly to_char(next_day(rad.created_on+15/24 - 7,2),'YYYY-MM-DD') to_char(时间列,'yyyy')||to_char(时间列,'IW')   --monthly to_char(rad.created_on+15/24,'yyyy-mm') --quarterly to_char(rad.created_on+15/24,'yyyy-q') ||'Q'   例如我的sql： 按天----select to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'YYYY-MM-dd') a,sum(num) from udbstat group by to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'YYYY-MM-dd'); 按周----select to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'ww') a,sum(num) from udbstat group by to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'ww'); 因为我的timestamp是字符串，故需要先转换成date类型。 如果字段本身就是date类型，sql如下： 按小时---select to_char(timestamp, 'YYYY-MM-dd hh24') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM-dd hh24'); 按天---select to_char(timestamp, 'YYYY-MM-dd') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM-dd'); 按月---select to_char(timestamp, 'YYYY-MM') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM'); 按年---select to_char(timestamp, 'YYYY') a,sum(num) from udbstat group by to_char(timestamp,'YYYY'); 按季度---select to_char(timestamp, 'YYYY-q') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-q'); 按周---select to_char(timestamp, 'ww') a,sum(num) from udbstat group by to_char(timestamp,'ww');