SQL WEIRD

需求：含有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

1. 避免重复计算
2. case when 使用时，在where处先过滤
3. 减少join

实际上mysql在生成执行计划的时候，其中有一个步骤，是确定表的join顺序。默认情况下，mysql会把所有join顺序全部排列出来，依次计算各个join顺序的执行代价并且取最优的那个。这样一来，n个表join会有n!种情况。十个表join就是10!，大概300万，所以难怪mysql要分析半天了。

其他容易忘记的...
union : 去重
union all 不去重