Mysql para seleccionar el registro mes a mes, incluso si los datos no existen

Escribí una consulta para obtener un registro mes a mes en la tabla de usuarios de la siguiente manera

SELECT COUNT( `userID` ) AS total, DATE_FORMAT( `userRegistredDate` , '%b' ) AS MONTH , YEAR( `userRegistredDate` ) AS year FROM `users` GROUP BY DATE_FORMAT( FROM_UNIXTIME( `userRegistredDate` , '%b' ) ) 

Salida:

 total MONTH year --------------------------- 3 May 2013 2 Jul 2013 -------------------------- 

Rendimiento esperado:

 total MONTH year --------------------------- 0 Jan 2013 0 Feb 2013 0 Mar 2013 0 Apr 2013 3 May 2013 0 Jun 2013 2 Jul 2013 -------------------------- 

Necesito mostrar el registro incluso si los datos no existen. ¿Como hacer esto?

No voy a decir mucho acerca de la eficiencia, ya que no lo he probado contra otros métodos, pero sin tener una tabla temporal esto parece un buen camino por recorrer.

  SELECT COUNT(u.userID) AS total, m.month FROM ( SELECT 'Jan' AS MONTH UNION SELECT 'Feb' AS MONTH UNION SELECT 'Mar' AS MONTH UNION SELECT 'Apr' AS MONTH UNION SELECT 'May' AS MONTH UNION SELECT 'Jun' AS MONTH UNION SELECT 'Jul' AS MONTH UNION SELECT 'Aug' AS MONTH UNION SELECT 'Sep' AS MONTH UNION SELECT 'Oct' AS MONTH UNION SELECT 'Nov' AS MONTH UNION SELECT 'Dec' AS MONTH ) AS m LEFT JOIN users u ON MONTH(STR_TO_DATE(CONCAT(m.month, ' 2013'),'%M %Y')) = MONTH(u.userRegistredDate) AND YEAR(u.userRegistredDate) = '2013' GROUP BY m.month ORDER BY 1+1; 

Si crea la unión basada en un formato de fecha, puede incluso reducir el trabajo y cargar la consulta.

  SELECT COUNT(u.userID) AS total, DATE_FORMAT(merge_date,'%b') AS month, YEAR(m.merge_date) AS year FROM ( SELECT '2013-01-01' AS merge_date UNION SELECT '2013-02-01' AS merge_date UNION SELECT '2013-03-01' AS merge_date UNION SELECT '2013-04-01' AS merge_date UNION SELECT '2013-05-01' AS merge_date UNION SELECT '2013-06-01' AS merge_date UNION SELECT '2013-07-01' AS merge_date UNION SELECT '2013-08-01' AS merge_date UNION SELECT '2013-09-01' AS merge_date UNION SELECT '2013-10-01' AS merge_date UNION SELECT '2013-11-01' AS merge_date UNION SELECT '2013-12-01' AS merge_date ) AS m LEFT JOIN users u ON MONTH(m.merge_date) = MONTH(u.userRegistredDate) AND YEAR(m.merge_date) = YEAR(u.userRegistredDate) GROUP BY m.merge_date ORDER BY 1+1; 

DEMO en vivo de ambas consultas.

Es posible que necesite una tabla para guardar cada registro de “mes”. Una tabla temporal puede hacer el truco:

 drop table if extists temp_months; create temporary table temp_months month date, index idx_date(month); insert into temp_months values ('2013-01-31'), ('2013-02-28'), ... 

Y ahora, puedes unir tus datos con esta tabla temporal recién creada:

 SELECT COUNT( `userID` ) AS total, DATE_FORMAT( m.month , '%b' ) AS MONTH , YEAR( m.month ) AS year FROM months as m left join `users` as u on m.month = last_day(FROM_UNIXTIME(`userRegistredDate`, '%b' ) GROUP BY last_day(m.month); 

Tenga en cuenta que puede poner la creación de tabla temporal (y llenar) en un procedimiento almacenado.

Utilizo last_day para simplificar, pero puede usar cualquier fecha del mes que desee, si se une correctamente.

Espero que esto ayude