MySQL COUNT - вернуть нулевой результат, а не NULL

Я пытаюсь вернуть набор результатов из MySql, который сгруппирован по ГОДУ и МЕСЯЦУ, и для которого возвращен счет за каждый ГОД / МЕСЯЦ.

Вот где я начал:

SELECT YEAR(p.pEndDate) AS pYear, MONTHNAME(p.pEndDate) AS pMonth, count(*) AS pNum 
FROM projects p
WHERE p.status=3
GROUP BY YEAR(p.pEndDate), MONTH(p.pEndDate)

Этот SQL в основном делает 90% от того, что мне нужно, за исключением случая, когда есть месяц, когда счетчик равен нулю. Например, в июле 2009 года было 0 проектов со статусом 3, поэтому я получаю:

2008    November    1
2009    January     2
2009    February    2
2009    March   2
2009    April   1
2009    May 2
2009    June    3
2009    August  2
2009    September   1
2009    October 1
2009    November    2
2009    December    1
2010    January 4
2010    February    1
2010    March   1
2010    April   3
2010    May 3
2010    June    3
2010    July    3
2010    August  3
2010    September   3
2010    October 2
2010    November    2
2010    December    3
2011    January 2
2011    February    1

Обратите внимание, что июля просто нет.

Поэтому я начал проводить некоторые исследования с использованием другой таблицы, чтобы включить в набор результатов июль. Поэтому я создал новую таблицу monthTable и добавил два столбца monthID int Primary Key, monthName VARCHAR(3).

Я пробовал много разных способов использования этой таблицы, начиная с RIGHT JOIN и т. Д. Ни один из них не дал успешных результатов, фактически почти все, что я делаю, дает тот же набор результатов, что и выше.

Любая помощь будет принята с благодарностью!

Источник

3 ответа

Решение

Я пробовал много разных способов использования этой таблицы [monthTable], начиная с RIGHT JOIN и т. Д. Ни один из них не дал успешных результатов, фактически почти все, что я делаю, дает тот же набор результатов, что и выше.

FROM projects p WHERE p.status=3

Я думаю, что вы пытались что-то вроде этого

FROM projects p
RIGHT JOIN monthTable m on <join p to m>
WHERE p.status=3`

Проблема в том, что предложение WHERE будет отфильтровывать все записи, которые не имеют значений p.status (null). Вам нужно переместить такие фильтры в предложение JOIN, как это

FROM projects p
RIGHT JOIN monthTable m on <join p to m> AND p.status=3`

Любопытно, но как достаточно таблицы, подобной, особенно с monthName, являющимся только varchar(3)?

monthID int Primary Key, monthName VARCHAR(3).

Попробуйте создать его так (один раз)

DROP PROCEDURE IF EXISTS FillMonthsTable;

delimiter //
CREATE PROCEDURE FillMonthsTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists monthsTable;
  create table monthsTable (theYear int, theMonth int, monthName varchar(20));

  SET @x := date('2000-01-01');
  REPEAT 
    insert into monthsTable (theyear, themonth, monthname) SELECT year(@x), month(@x), monthname(@x);
    SET @x := date_add(@x, interval 1 month);
    UNTIL @x > date('2030-01-01') END REPEAT;
END//
delimiter ;

CALL FillMonthsTable;

DROP PROCEDURE FillMonthsTable;

Затем, используя этот запрос (1 проход, чтобы сгруппировать ваши данные, затем левое соединение, чтобы получить 0)

SELECT m.theYear, m.theMonth, IFNULL(t.pNum, 0) theCount
FROM monthsTable m
LEFT JOIN (
    SELECT YEAR(p.pEndDate) AS pYear, MONTH(p.pEndDate) AS pMonth, count(*) AS pNum 
    FROM projects p
    WHERE p.status=3
    GROUP BY YEAR(p.pEndDate), MONTH(p.pEndDate)
) t on t.pYear = m.theYear and t.pMonth = m.theMonth
ORDER BY m.theYear, m.theMonth
Источник

Если у вас есть вспомогательная таблица с именем nums с целыми числами от 0 до 9 вы можете генерировать непрерывные последовательности любого типа. Ваша проблема не в том, что счетчик является нулевым для значения даты, а в том, что значение даты вообще не существует. Допустим, вы хотите ежемесячные подсчеты в период с января 2004 г. по март 2006 г., вы можете создать временный список дат, используя nums таблица как это:

SELECT DISTINCT ADDDATE('2004-01-01',INTERVAL i.i+j.i+k.i MONTH) AS mydate
FROM nums i JOIN nums j  JOIN nums k ORDER BY mydate LIMIT 27;

Затем, как описано в другом месте, вы присоединяете свои реальные данные к списку дат ON (год = год И месяц = ​​месяц).

Вот аналогичный запрос, выполненный на моей собственной таблице (msds) для иллюстрации:

select year(mydate) theyear, monthname(mydate) themonth, coalesce(c,0) thecount
from

(select DISTINCT adddate('2004-01-01',INTERVAL i.i+j.i+k.i MONTH) as mydate
FROM ints i  JOIN ints j  join ints k ORDER BY mydate LIMIT 27) datelist

left join

(SELECT year(issue_date) as y, month(issue_date) as m, count(*) c FROM msds m where issue_date between '2004-01-01' and '2006-03-01'
group by y, m) mydata

on (year(mydate)=y and month(mydate)=m)
Источник

Расширяя оператор OMG Ponies, вам нужна таблица Numbers или Tally, которая включает последовательный список целых чисел, который охватывает ваши месяцы и годы за все годы, по которым вы хотите выполнить запрос.

Create Table Numbers ( Value int not null Primary Key )
Insert Numbers(Value) Values( 1 )
Insert Numbers(Value) Values( 2 )
...
Insert Numbers(Value) Values( 12 )
...
Insert Numbers(Value) Values( 2000 )
Insert Numbers(Value) Values( 2001 )
...
Insert Numbers(Value) Values( 2011 )
Insert Numbers(Value) Values( 2012 )

Это будет одноразовая вставка, и таблица будет оставаться статичной до тех пор, пока вам не понадобится больше месяцев или лет. При этом мы теперь оставляем соединение вашей таблицы Projects с таблицей Numbers:

Select Years.Value As PYear
    , Month_Name( Date_Add('2000-01-01', Interval Months.Value - 1 MONTH) ) As PMonth
    , Count( P.NonNullableCol ) As PNum
From Numbers As Months
    Cross Join Numbers As Years
    Left Join Projects As P
        On Year( P.PEnddate ) = Years.Value
            And Month( P.PEndDate ) = Months.Value
Where Months.Value Between 1 And 12
    And Years.Value Between 2008 And 2011
Group By Years.Value, Months.Value

прибавление

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

Create Table Calendar ( DateValue date not null Primary Key )
Insert Calendar( DateValue ) Values( '2000-01-01' )
Insert Calendar( DateValue ) Values( '2000-01-02' )
Insert Calendar( DateValue ) Values( '2000-01-03' )
...
Insert Calendar( DateValue ) Values( '2011-03-01' )

Select Year( C.DateValue ) As PYear
    , Month( C.DateValue ) As PMonth
    , Count( P.NonNullableCol ) As PNum
From Calendar As C
    Left Join Projects As P
        On P.PEndDate = C.DateValue
Where C.DateValue Between '2008-11-01' And '2011-02-28'
Group By Year( C.DateValue ), Month( C.DateValue )
Источник
Другие вопросы по тегам