3 回答

TA貢獻1804條經驗 獲得超8個贊
SELECT DATE_FORMAT(ec_salesorder.duedate,’%Y-%m’) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m,也就是把duedate日期以月的形式顯示,然后groupby,那么按周如何統計呢?
搜了一下mysql的manual,在這里找到一個解決方法,通過mysql的week函數來做,sql語句如下:SELECT WEEK(ec_salesorder.duedate) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m,這個方法有個缺陷,不能顯示年份,僅僅靠一個周數不方便查看統計信息。

TA貢獻1802條經驗 獲得超10個贊
以周一作為一周的開始, 使用mysql week行數模式5:
作為周劃分的標準, 比如20170101是周天, week(20170101, 5) = 0,
分WEEK_IN_MONTH, 和WEEK_IN_YEAR兩種情況進行分組:
WEEK_IN_YEAR:
1 2 3 4 5 6 7 8 9 10 11 12 13 | # by sleest 2017/03/29 按每個日期所在一年中的第幾周分組匯總 SELECT WEEK(MY_DATE, 5)+1 AS WEEK_OF_YEAR, COUNT(1) AS COUNT, GROUP_CONCAT(MY_DATE) AS INCLUDE_DATE FROM (SELECT '2017-01-01' AS MY_DATE UNION ALL SELECT '2017-02-08' UNION ALL SELECT '2017-02-03' UNION ALL SELECT '2017-02-01' UNION ALL SELECT '2017-01-21') TMP GROUP BY WEEK(MY_DATE, 5) + 1; |
結果:
WEEK_IN_MONTH:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # by sleest 2017/03/29 按每個日期所在每個月的第幾周分組匯總 SELECT WEEK(MY_DATE, 5) - WEEK(DATE_SUB(MY_DATE, INTERVAL DAYOFMONTH(MY_DATE) - 1 DAY), 5) + 1 AS WEEK_OF_MONTH, COUNT(1) AS COUNT, GROUP_CONCAT(MY_DATE) AS INCLUDE_DATE FROM (SELECT '2017-01-01' AS MY_DATE UNION ALL SELECT '2017-02-08' UNION ALL SELECT '2017-02-03' UNION ALL SELECT '2017-02-01' UNION ALL SELECT '2017-01-21') TMP GROUP BY WEEK(MY_DATE, 5) - WEEK(DATE_SUB(MY_DATE, INTERVAL DAYOFMONTH(MY_DATE) - 1 DAY), 5) + 1 |
結果:
添加回答
舉報