MySQL:月份范围连接

Question

我有两个表:

**users**
id  firstname   lastname    
1   John        Mathews
2   Scott       Mithchell
....
....
....


**publish**
id  userid      bookid      published
1   1       1       2013-01-16 14:12:10
1   2       1       2013-01-08 15:17:40
1   2       1       2013-01-10 14:18:10
....
....
....

我需要根据字段“已发布”查找每个用户每个月(一月、二月、三月......等)的记录。如果该用户相关月份没有数据，则应显示 0(零)。所以输出应该是这样的:-

id  firstname   lastname    month       published
1   John        Mathews     January     1
1   John        Mathews     february    2
1   John        Mathews     march       1
1   John        Mathews     april       1
1   John        Mathews     may         1
1   John        Mathews     june        1
1   John        Mathews     july        0
1   John        Mathews     august      0
1   John        Mathews     september   0
1   John        Mathews     October     0
1   John        Mathews     November    0
1   John        Mathews     December    1
2   Scott       Mitchell    January     2
2   Scott       Mitchell    february    2
2   Scott       Mitchell    march       1
2   Scott       Mitchell    april       1
2   Scott       Mitchell    may         1
2   Scott       Mitchell    june        0
2   Scott       Mitchell    july        0
2   Scott       Mitchell    august      1
2   Scott       Mitchell    september   1
2   Scott       Mitchell    October     0
2   Scott       Mitchell    November    0
2   Scott       Mitchell    December    1

Answer 1

** 个月 **

nr | monthName
----------
1  | january
2  | february
...

然后:

SELECT p.id, fristname, lastname, m.monthName AS month, count(*) as published
FROM months m LEFT JOIN publish p ON MONTH(p.published) = m.nr
JOIN users u ON u.id = p.userid
GROUP BY p.id, firstname, lastname, month
ORDER BY ...