MySQL Query: выберите существующие месяцы из базы данных и завершите динамически с оставшимся месяцем

Question

У меня есть две таблицы:

CREATE TABLE 'sales_sheet' (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_typed_by` int(11) DEFAULT NULL,
  `_product_id` int(11) DEFAULT NULL,
  `_year` date DEFAULT NULL,
  `_validation_state` int(11) DEFAULT NULL,
  PRIMARY KEY  (`_id`),
  KEY `_product_id` (`_product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE 'sales_sheet_entries' (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_sheet_id` int(11) DEFAULT NULL,
  `_month` date DEFAULT NULL,
  `_quantity` int(11) DEFAULT NULL,
  PRIMARY KEY  (`_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Эти две таблицы используются для хранения листов годовых продаж. В первой таблице хранится идентификатор продукта, год и статус проверки листа. Второй хранит ежемесячные продажи товара.

Я хочу, чтобы в одном запросе получить такой результат:

---------------------------------------------
| Month         | Sales volume  |
---------------------------------------------
| January 2010      |       |
| February 2010     | XXXXXX    |
| March 2010        | XXXXXX    |
| April 2010        | XXXXXX    |
| May 2010          |       |
| June 2010         |       |
| July 2010         |       |
| August 2010       |       |
| September 2010        |       |
| October 2010      | XXXXXX    |
| November 2010     | XXXXXX    |
| December 2010     |       |
---------------------------------------------

Пустые поля в столбце объема продаж могут соответствовать записи, уже сохраненной в таблице sales_sheet_entries, но не имеют соответствующего объема продаж или просто отсутствуют в базе данных, но запрос должен отображать его.

Одно из моих ограничений заключается в том, что я не могу поместить 12 столбцов, которые соответствуют списку месяцев в таблице sales_sheet, так как спецификации клиента меняются, и он может потребовать, чтобы объем продаж был заполнен периодом, а не месяцем.

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

Answer 1

MySQL не имеет рекурсивной функциональности, поэтому вам нужно использовать табличный трюк NUMBERS -

1. Создайте таблицу, которая содержит только увеличивающиеся числа - это легко сделать с помощью auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Заполните таблицу, используя:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ... столько ценностей, сколько вам нужно.

3. Используйте DATE_ADD, чтобы составить список раз, увеличив месяцы на основе значения NUMBERS.id:

   SELECT x.*
     FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH)
             FROM numbers n) x
   

4. СЛЕДУЮЩИЙ ПРИСОЕДИНЯЙТЕСЬ к своей таблице данных, основанной на части даты и времени:

      SELECT DATE_FORMAT(x.dt, '%M %Y') AS Month,
             y.sales_volume
        FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH) AS dt
                FROM numbers n) x
   LEFT JOIN (SELECT ss._product_id,
                     ss._year,
                     sse._month,
                     SUM(sse._quantity) AS sales_volume
                FROM SALES_SHEET ss
                JOIN SALES_SHEET_ENTRIES sse ON sse._sheet_id = ss._id
             GROUP BY ss._product_id, ss._year, sse._month) y ON y._month = MONTH(x.dt)
                                                             AND y._year = YEAR(x.dt)
                                                             AND y._product_id = ?