SQL WEIRD

作者: 咸鱼干lili | 来源:发表于2019-05-22 21:42 被阅读0次

需求:含有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 不去重

相关文章

  • SQL WEIRD

    需求:含有GROUP BY子句的查询中如何显示COUNT()为0的结果 count() 忽略NULL 例子1: h...

  • Weird

    兜兜转转,又回到了起点。还是那么的迷茫,不知道自己究竟追求的是什么,忘记了自己奋斗的目标。渐渐停下了自己的脚步。是...

  • Weird

  • weird

    busyed of saturday all day and tired,i want to go to sl...

  • WEIRD

    Weird,是英文中的常用词,意为不可思议的;怪诞的,超自然的;古时用法有有“命运的,宿命的”之意。 那么字母全部...

  • Go map

    The syntax seems confusingly weird...

  • Some thoughts

    Sometimes,looking at the clouds with weird shapes in the ...

  • Weird Weather

    有时晴 有时雨 今天发现了一盆

  • A Weird Dream

    I had a weird dream.A boy and a girl met each other by on...

  • A weird man

    为了帮助学生巩固单词,我试着把学过的动词串在一起编成故事,没想到编着编着竟然来了灵感.... Max is a y...

网友评论

      本文标题:SQL WEIRD

      本文链接:https://www.haomeiwen.com/subject/gtmpzqtx.html