• 【MySQL】分组汇总实例(面试题入门到拓展)


    原文:看似简单的一道SQL面试题,你是否能够很快写出答案?

    黄伟呢 数据分析与统计学之美  2020-06-13

     

    https://mp.weixin.qq.com/s/BBsN46t1KQ3tW13gLdBmcw

    原表数据

    目标表输出:

    -- 创建表
    
    create table student (
         id varchar(20),
         name varchar(20),
         gender char(1),
         birth varchar(20),
         department varchar(20),
         address varchar(20)
    ) charset = utf8;
    
    -- 插入数据
    insert into student values
    ("201901","张大佬","男","1985","计算机系","北京市海淀区"),
    ("201902","郭大侠","男","1986","中文系","北京市昌平区"),
    ("201903","张三","女","1990","中文系","湖南省永州市"),
    ("201904","李四","男","1990","英语系","辽宁市阜新市"),
    ("201905","王五","女","1991","英语系","福建省厦门市"),
    ("201906","王六","男","1988","计算机系","湖南省衡阳市");

     第一步分组:

    select 
        department 院系,
        case gender when "男" then 1 else 0 end 男,
        case gender when "女" then 1 else 0 endfrom student;

    第二部汇总:外面套一层

    select 
         院系,
         sum(男) 男,
         sum(女) 女,
         sum(男) + sum(女) as 总计
    from
    (
        select department 院系,
        case gender when "男" then 1 else 0 end 男,
        case gender when "女" then 1 else 0 endfrom student
    ) a
    group by 院系;

    原解题思路,详见上述公众号

    -----------------------分割线------------------------------

     以上思路比较清晰易懂,但是需要执行两次查询,如果数据量比较大,比如超过50W条记录时,查询效率不高。

    以下为个人改进版

    -- 分组汇总
    
    select 
        department 院系,
        sum(case gender when "男" then 1 else 0 end) as 男,
        sum(case gender when "女" then 1 else 0 end) asfrom student
    group by department;

     套用公式:

    sum(case ...when...then...else...end) as xxx

    其中sum,可以改成任意集合函数,如count,max,min等等

    有人可能会说,decode也可以啊,decode只能用在oracle

    -- Oracle decode写法
    
    select 
        department 院系,
        sum(decode(gender ,”男” ,1 , 0 )) as 男,
        sum(decode(gender ,”女” ,1 , 0)) asfrom student
    group by department;

    如果要在MySQL实现上面的方法,除了case when then else end,

    还有if和decode相似,感谢群友(深圳-小小明)提供思路

    select 
        department 院系,
        sum(if(gender ="男" ,1, 0)) as 男,
        sum(if(gender ="女" ,1, 0)) asfrom student
    group by department;

     未聚合

    select 
        department 院系,
        if(gender ="男" ,1, 0) as 男,
        if(gender ="女" ,1, 0) asfrom student;

    ---------------------------

    再拓展下

    加上合计:count(gender),或者sum(1),因为gender字段本身不具备数学运算

    如果gender字段为空,合计可能要调整,具体还得看实际业务,不在本次考虑范围内。

     学会了没,是不是很简单

  • 相关阅读:
    进程DLL注入
    静态链接库LIB
    利用MoveFileEx实现程序的隐藏、自启动与自删除
    QueueUserApc实现DLL注入的测试
    简单说说SSDT
    ural 1521. War Games 2 约瑟夫环 SBT实现
    次小生成树 (附:poj1679)
    hoj 1138 LC Display
    hoj 3029 Dictionary 模拟队列
    hoj 2578 Super_Stack 模拟栈
  • 原文地址:https://www.cnblogs.com/hightech/p/13112263.html
Copyright © 2020-2023  润新知