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

131

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.