mysql вопрос по датам

100
13 ноября 2019, 01:20

Добрый! Следующим запросом делаю листинг статистики

SELECT SUM(  `bonus` ) , DATE_FORMAT(  `make` ,  '%d %m %Y' ) 
FROM  `trans` 
GROUP BY DAYOFYEAR(  `make` ) 
ORDER BY DAYOFYEAR(  `make` ) DESC 

Проблема в том что если за какой то день в базе не было записей, то этот день в выборке отсутствует, как сделать что бы в выборку попадали все дни по порядку, но в случае отсутствия записей напротив такого дня сумма была 0

Answer 1

Нам надо создать опорный запрос, который выдаст все даты за требуемый интервал времени к которому потом доклеить наши данные. Для создания такого запроса в MySQL проще всего воспользоваться рабочей табличкой с порядковыми номерами (такая таблица достаточно часто используется в разных ситуациях). Номеров в таблице должно быть достаточно что бы покрыть весь требуемый диапазон времени. Ваш запрос явно рассчитан не более чем на год (из за dayofyear) так что 366 записей было бы достаточно.

create table seqnum(X int not null);
-- Первые 8 записей
insert into seqnum values(0),(1),(2),(3),(4),(5),(6),(7);
-- И еще 512
insert into seqnum
select s1.x*64+s2.x*8+s3.x+8
  from seqnum s1, seqnum s2, seqnum s3;

Ваш запрос для генерации календаря с количествами за весь 2016 год будет выглядеть так:

select DATE_FORMAT('2016.01.01'+interval S.X day,'%d %m %Y'),
       SUM(bonus)
  from seqnum S
  left join `trans` on date(`make`)='2016.01.01'+interval S.X day
  where X<366
 group by S.X
 order by S.X

В принципе можно конечно обойтись и без таблицы seqnum, но запрос будет дико громоздким на подобии select 1 x union select 2 ... union select 7 повторенный три раза и с формулой дня как я использовал при начальной генерации seqnum.

READ ALSO
Обработка исключений и вывод в JSON

Обработка исключений и вывод в JSON

У меня есть API в котором нужно возвращать ошибки клиенту с помощью исключенийНапример, клиент запросил несуществующий документ и приложение...

99
Как поменять кодировку в php коде?

Как поменять кодировку в php коде?

Есть файл с кодом который вызывает rss для показа на сайтеНо кодировка rss

92
Кнопка нравится с использованием cookie

Кнопка нравится с использованием cookie

Есть код кнопки нравитсяНа данный момент один пользователь может нажимать на кнопку любое количество раз

85
Оптимизировать цикл парсинга

Оптимизировать цикл парсинга

Есть некоторый цикл (представлен частично от основного кода, но суть передает):

100