Выбор между диапазоном дат и установление пустых строк в 0?

Question

У меня есть стол, который выглядит как

expires    | value  
-------------------
2011-06-15 | 15  
2011-06-15 | 15  
2011-06-25 | 15  
2011-07-15 | 15  
2011-07-15 | 15  
2011-07-25 | 15  
2011-08-15 | 15  
2011-08-15 | 15  
2011-08-25 | 15

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

June   | 45  
July   | 45  
August | 45  

Так что мой запрос

  SELECT SUM(amount) AS `amount`, 
         DATE_FORMAT(expires , '%M') AS `month`  
    FROM dealDollars 
   WHERE DATE(expires) BETWEEN DATE(NOW()) 
                           AND LAST_DAY(DATE(NOW()+INTERVAL 3 MONTH)) 
GROUP BY MONTH(expires)

Который работает отлично. Но в результате, если бы в июле не было строк, июль не появился бы.

Как я могу заставить июль отображаться с 0 в качестве значения?

Answer 1

Нет простого способа сделать это. Один из возможных способов - создать таблицу с именем month: которая будет иметь 12 строк: (январь, февраль, ..., декабрь)

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

Answer 2

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)
   

   ... столько ценностей, сколько вам нужно. В этом случае оператор INSERT должен выполняться не менее 3 раз.

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

   SELECT x.dt
     FROM (SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) AS dt
             FROM numbers n
            WHERE DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) BETWEEN CURRENT_DATE()
                                                                                AND LAST_DAY(CURRENT_DATE() +INTERVAL 3 MONTH))  ) x
   

4. Используйте OUTER JOIN, чтобы получить желаемый результат:

      SELECT x.dt,
             COUNT(*) AS total
        FROM (SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) AS dt
             FROM numbers n
            WHERE DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) BETWEEN CURRENT_DATE()
                                                                                AND LAST_DAY(CURRENT_DATE() +INTERVAL 3 MONTH))  ) x
   LEFT JOIN YOUR_TABLE y ON y.date = x.dt
    GROUP BY x.dt
    ORDER BY x.dt
   

Почему цифры, а не даты?

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

Answer 3

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

Вам придется работать над спецификой самостоятельно, но вы рассматривали подзапросы в предложении from?

По сути, это было бы что-то вроде этого:

SELECT NVL(B.amount, 0) as `amount`, A.month as `month`
      FROM (SELECT 'January' as `month`
            UNION SELECT 'February' as `month`
            UNION SELECT 'March' as `month`...
            UNION SELECT 'DECEMBER' as `month`) as A
      LEFT JOIN
            (SELECT SUM(amount) AS `amount`, 
                    DATE_FORMAT(expires , '%M') AS `month`  
               FROM dealDollars 
               WHERE 
                  DATE(expires) BETWEEN 
                       DATE(NOW()) AND 
                       LAST_DAY(DATE(NOW()+INTERVAL 3 MONTH)) 
               GROUP BY MONTH(expires)) as B
            ON (A.MONTH = B.MONTH)

Сумасшедший, нет?

Answer 4

select MONTHNAME(expires) as month_name,sum(`value`) from Table1
group by month_name order by null;

играть на скрипке