Рассмотрим следующую таблицу
mysql> select * from cart ;
+------+------------+-------------+
| id | date | total_price |
+------+------------+-------------+
| 1 | 2014-01-01 | 10 |
| 2 | 2014-01-20 | 20 |
| 3 | 2014-02-03 | 30 |
| 4 | 2014-02-28 | 40 |
| 5 | 2014-06-01 | 50 |
| 6 | 2014-06-13 | 24 |
| 7 | 2014-12-12 | 45 |
| 8 | 2014-12-18 | 10 |
+------+------------+-------------+
Теперь, согласно логике, вы оглядываетесь назад на один год, и december
появится в результате дважды, т.е. dec 2013 and dec 2014
, и если нам нужно иметь отдельный счет для них, мы можем использовать следующую технику создания динамического диапазона дат MySql Single Table, выберите последние 7 дней и включите пустые строки
t1.month,
t1.md,
coalesce(SUM(t1.amount+t2.amount), 0) AS total
from
(
select DATE_FORMAT(a.Date,"%b") as month,
DATE_FORMAT(a.Date, "%m-%Y") as md,
'0' as amount
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
group by md
)t1
left join
(
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as amount ,DATE_FORMAT(date, "%m-%Y") as md
FROM cart
where Date <= NOW() and Date >= Date_add(Now(),interval - 12 month)
GROUP BY md
)t2
on t2.md = t1.md
group by t1.md
order by t1.md
;
Выход будет
+-------+---------+-------+
| month | md | total |
+-------+---------+-------+
| Jan | 01-2014 | 30 |
| Feb | 02-2014 | 70 |
| Mar | 03-2014 | 0 |
| Apr | 04-2014 | 0 |
| May | 05-2014 | 0 |
| Jun | 06-2014 | 74 |
| Jul | 07-2014 | 0 |
| Aug | 08-2014 | 0 |
| Sep | 09-2014 | 0 |
| Oct | 10-2014 | 0 |
| Nov | 11-2014 | 0 |
| Dec | 12-2013 | 0 |
| Dec | 12-2014 | 55 |
+-------+---------+-------+
13 rows in set (0.00 sec)
И если вас не волнует вышеуказанный случай, т.е. dec 2014 and dec 2013
Затем просто измените group by
в динамической части даты как
where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
group by month
и окончательная группа как group by t1.month
person
Abhik Chakraborty
schedule
22.12.2014
SELECT
иGROUP BY
? - person vladkras   schedule 22.12.2014ORDER BY
, группировка будет работать и сJun
, и сAug
. - person vladkras   schedule 22.12.2014