mysql - 即使记录不存在也返回行 使用 group by 子句进行 LEFT 连接

Question

我有两张 table

表格引线

customer_id  created_on             rating_id
1889         2015-02-26 06:24:54    2
1890         2015-03-02 11:27:16    3
1892         2015-03-03 08:19:30    2
1899         2015-03-09 10:56:24    2
1902         2015-03-10 07:21:40    2
1904         2015-03-20 07:28:05    2
1908         2015-04-01 12:34:47    4
1909         2015-04-06 03:29:26    4
1922         2015-04-27 07:00:36    3
1926         2015-04-29 10:31:06    2
1929         2015-05-01 11:59:08    1
1931         2015-05-05 08:49:38    1
1933         2015-05-05 08:56:17    1
1939         2015-05-05 10:29:45    1
1940         2015-05-05 10:36:20    1

表评级

rating_id   rating
1           Cold
2           Warm
3           Hot
4           Lost

我想根据评级表中一年中所有月份的评级来获取所有潜在客户的计数，为此我正在使用以下查询

SELECT COUNT(t2.rating_id),MONTHNAME(t1.created_on),t2.rating,t1.rating_id FROM customer_detail t1
RIGHT JOIN master_lead_rating t2 ON t1.rating_id = t2.rating_id 
WHERE t1.is_lead = 1 AND YEAR(t1.created_on) =2015 GROUP BY STR_TO_DATE(t1.created_on, '%Y-%m'),t2.rating_id

但是这个查询的结果是

COUNT(t2.rating_id) MONTHNAME(t1.created_on)    rating  rating_id
1                   February                    Warm    2
4                   March                       Warm    2
1                   March                       Hot     3
1                   April                       Warm    2
1                   April                       Hot     3
1                   April                       Lost    4
5                   May                         Cold    1

我想显示评分为 null 或 0 的计数，如果不存在，就像二月份那样，应该会出现计数为 0 或 null 的 Hot/Lost/Cold 行。我该怎么做？

Answer 1

添加此表；

create table `cal` (
    `id` int,
    `cal_name` varchar (60)
); 

insert into `cal` (`id`, `cal_name`) values ('1', 'January'),('2', 'February'),('3', 'March'),('4', 'April'),('5', 'May'),('6', 'June'),('7', 'July'),('8', 'August'),('9', 'September'),('10', 'October'),('11', 'November'),('12', 'December');

然后执行此查询；

select cal.cal_name, count(rating_id), master_lead_rating.rating, master_lead_rating.rating_id
from cal
left join customer_detail on month(customer_detail.created_on) = cal.id
left join master_lead_rating on master_lead_rating.rating_id = customer_detail.rating_id
group by id

99%确定它有效，在我完成测试之前sqlfiddle就死了。