MySql左连接无法显示左表全部数据

• left join on 后边的条件只针对副表，如果要筛选主表必须写在where后。

# 问题查不出左表全部数据
select * from a left join b on a.id=b.id where b.name="kk";
# 解决问题
select * from a left join b on a.id=b.id and b.name="kk";

• 左连接后，使用聚合函数（max、count、sum）未指定分组 group by。

# 问题查不出左表全部数据
SELECT DATE_FORMAT(a.date,'%m-%d') as happenDate, IFNULL(count(b.id),0) num
FROM (
	select DATE_FORMAT(date_sub(now(), interval 0 DAY),'%Y-%m-%d') date
	UNION ALL
	SELECT  DATE_FORMAT(date_sub(now(), interval 1 day),'%Y-%m-%d') date
	UNION ALL
	SELECT  DATE_FORMAT(date_sub(now(), interval 2 day),'%Y-%m-%d') date
	UNION ALL
	SELECT  DATE_FORMAT(date_sub(now(), interval 3 day),'%Y-%m-%d') date
	UNION ALL
	SELECT  DATE_FORMAT(date_sub(now(), interval 4 day),'%Y-%m-%d') date
	UNION ALL
	SELECT  DATE_FORMAT(date_sub(now(), interval 5 day),'%Y-%m-%d') date
	UNION ALL
	SELECT  DATE_FORMAT(date_sub(now(), interval 6 day),'%Y-%m-%d') date
) a
LEFT JOIN jy_push_alarm_terminal b
ON DATE_FORMAT(b.update_time,'%Y-%m-%d') = a.date AND b.scene = 1 AND b.del_flag = 0
# 解决问题，就是对a.date进行分组
GROUP BY a.date