以下列数据库表中的数据为例来说明按年、月、周、日的分组查询:
按年查询:
SELECT DATE_FORMAT(t.time,'%Y') year_time,sum(t.quantity) total FROM `time_demo` t GROUP BY year_time;
结果为:
按月查询:
SELECT DATE_FORMAT(t.time,'%Y-%m') month_time,sum(t.quantity) total FROM `time_demo` t GROUP BY month_time;
结果为:
按周查询:
SELECT DATE_FORMAT(t.time,'%Y-%u') week_time,sum(t.quantity) total FROM `time_demo` t GROUP BY week_time;
结果为:
当然也可以把周包装成更可读的展示,如下:
SELECT CONCAT(SUBSTR(DATE_FORMAT(t.time,'%Y-%u') FROM 1 FOR 4),'第',SUBSTR(DATE_FORMAT(t.time,'%Y-%u'),6),'周') week_time,sum(t.quantity) total FROM `time_demo` t GROUP BY week_time;
结果为:
按日查询:
SELECT DATE_FORMAT(t.time,'%Y-%m-%d') day_time,sum(t.quantity) total FROM `time_demo` t GROUP BY day_time;
结果为:
总结:
主要是对DATE_FORMAT(date,format)这个函数的使用:date 参数是日期;format 规定日期/时间的输出格式。