消费数据不存在或者mysql,MySQL选择每月记录，即使数据不存在

I wrote a query to get month-wise record in user table as follows

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' ) )

Output:

total MONTH year

---------------------------

3 May 2013

2 Jul 2013

--------------------------

Expected Output:

total MONTH year

---------------------------

0 Jan 2013

0 Feb 2013

0 Mar 2013

0 Apr 2013

3 May 2013

0 Jun 2013

2 Jul 2013

--------------------------

I need to show the record even if data not exist. How to do this?

解决方案

I won't say much about efficiency as I have not tested it against other methods but without having a temp table this seem a fair way to go.

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;

If you make the union based on a date format you can even reduce the work and load on the query.

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;