mysql通过某个字段分组统计数据

mysql通过某个固定状态字段分组统计数据, 字段状态不存在的统计数量为0

如下有个案例需求, 有如下user表, 需要根据status字段进行分组统计, 分别统计出: 待提交, 待审核, 通过, 驳回的条数, 以及每个状态的占比, 如果某个状态在数据库没有值, 则统计数量为0, 占比为0

创建user表的DDL语句如下

#创建表
create table user
(
    id        int auto_increment
        primary key,
    user_name varchar(20) null,
    money     int         null,
    password  varchar(30) null,
    status    char(1) comment '状态(0:待提交, 1:待审核, 2:通过, 3:驳回)');
#添加测试数据
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (1, '李四', 100, null, '1');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (2, '李四', 100, null, '2');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (3, '张三', 500, null, '3');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (4, '张三', 500, null, '2');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (5, 'zs', 10000, '123', '1');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (6, 'ls', null, '123', '2');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (7, 'admin', null, '123', '2');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (8, 'ah', null, '123', '1');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (9, 'ahuo', null, '123', '3');
INSERT INTO practice.user (id, user_name, money, password, status) VALUES (10, '1', null, '123', '2');

拿到这个需求首先就是想到用group by, 但是对于我们刚刚入职没多久的小小程序员来说, 通过group by分组没有问题, 问题就在于如果某个状态在数据库中并没有值, 单纯使用group by无法筛选出无值的数据, 具体sql及结果如下
sql:

select COUNT(*) as num,
       status,
       round(COUNT(*) / (select count(*) as total from user where user_name is not null), 2) as persent
from user
where user_name is not null
group by status

执行结果:

果然, 我们发现当一种状态在数据库中不存在数据的时候, 单纯使用单表的group by无法实现我们的需求, 这个状态在数据库没有数据, 所以根本不会查出, 这违背了我们的需求, 如果我们把这种数据返回给前端, 你就等着前端妹妹来找你吧! 如果想要多聊天增进感情的算我没说(狗头滑稽)

那应该怎么做呢?
此处, 我给出一种解决办法

首先我们先按照我们想要的数据结构先拼接出一个表结构, 那我们想要的是什么结构呢, 当然是所有的status的状态是全的, 如下图所示, 请执行如下sql, 自行查看, 通过union all拼接出一个表结构当做主表然后关联一个子表写业务即可实现啦!

select '0' as status
      union all
      select '1' as status
      union all
      select '2' as status
      union all
      select '3' as status

最终sql如下, 如果大家有其他好的想法, 请评论留言! 本人一定好好学习! 谢谢!

select a.status,
       ifnull(b.num, 0)     as num,
       ifnull(b.persent, 0) as persent
from (select '0' as status
      union all
      select '1' as status
      union all
      select '2' as status
      union all
      select '3' as status) a
         left join (
    select COUNT(*) as num,
           status,
           round(COUNT(*) / (select count(*) as total from user where user_name is not null), 2) as persent
    from user
    where user_name is not null
    group by status
) b on a.status = b.status

完美解决~