需求：含有GROUP BY子句的查询中如何显示COUNT()为0的结果
- count() 忽略NULL
例子1： https://blog.csdn.net/evasunny2008/article/details/52525196
SELECT CategoryID ,
COUNT(1) AS TotalNum
FROM ExampleTable
WHERE flag = 1
GROUP BY CategoryID;
SELECT DISTINCT
MainTable.CategoryID ,
ISNULL(SubTable.SubNum, 0) AS TotalNum
FROM ExampleTable AS MainTable
LEFT JOIN (
SELECT CategoryID ,
COUNT(1) AS SubNum
FROM ExampleTable
WHERE flag = 1
GROUP BY CategoryID
) AS SubTable ON MainTable.CategoryID = SubTable.CategoryID;
例子2: https://blog.csdn.net/pangliang_csdn/article/details/51878586
select count(*) num,user_id
from order_manage
here seller_id in(select user_id from user_info )
group by user_id
order by num DESC
select k.user_id uid,count(m.user_id) num
from (select user_id from user_info ) k
left join (select user_id from order_manage )
m on k.user_id = m.user_id
group by k.user_id
order by num DESC
left join on 与 where 的区别
https://blog.csdn.net/jihuanliang/article/details/17362767、
https://blog.csdn.net/u013468917/article/details/61933994
SQL 性能优化
参考链接： https://www.jianshu.com/p/c5f4c53a2344
- 避免重复计算
- case when 使用时，在where处先过滤
- 减少join
实际上mysql在生成执行计划的时候，其中有一个步骤，是确定表的join顺序。默认情况下，mysql会把所有join顺序全部排列出来，依次计算各个join顺序的执行代价并且取最优的那个。这样一来，n个表join会有n!种情况。十个表join就是10!，大概300万，所以难怪mysql要分析半天了。
其他容易忘记的...
union : 去重
union all 不去重
