1、MySQL及sum case when统计实战讲解

MySQL中使用sum case when统计实战讲解

1、场景：

车险,分组织统计某一时间段内报价的车辆数、出单数、出单保费金额、A端出单数、B端出单数

2、涉及表：

• 报价订单表

• 组织机构表

• 车辆信息表

  建表语句如下

• 报价订单表

create table y_quote_order
(
    id                      bigint auto_increment comment 'id'
        primary key,
    batch_no                varchar(150) default ''                not null comment '报价批次号，若为批量报价，此字段存报价批次号',
    order_no                varchar(150) default ''                not null comment '报价订单号',
    total_premium           decimal(18, 2)                         null comment '保费+服务包费',
    auto_id                 bigint                                 not null comment '车辆信息',
    status                  tinyint(2)                             not null comment '状态',
    create_time             datetime     default CURRENT_TIMESTAMP not null comment '创建时间',
    update_time             datetime     default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
    product_premium         decimal(18, 2)                         null comment '商品金额',
    total_insurance_premium decimal(18, 2)                         null comment '总车险保费',
    non_auto_premium decimal(18, 2)                                null comment '非车险保费',
    client_type             int                                    null comment '业务入口 1-C端，2-A端',
    organization_id         bigint       default 0                 not null comment '组织机构id'
)
    comment '报价订单表';

create index idx_organization_id
    on quote_order (organization_id)
    comment '组织机构id'; 

• 组织机构表

create table y_organization
(
    id          bigint auto_increment comment 'id'
        primary key,
    name        varchar(100) default ''                not null comment '名称',
    code        varchar(45)  default ''                null comment '编码',
    short_code  varchar(20)                            null comment '顶级组织简称',
    short_name  varchar(45)  default ''                not null comment '简称',
    level       int                                    null comment '层级',
    parent_code varchar(45)  default '0'               not null comment '组织code',
    operator    varchar(50)  default ''                not null comment '操作人',
    create_time datetime     default CURRENT_TIMESTAMP not null comment '创建时间',
    update_time datetime     default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
    constraint uniq_short_name
        unique (short_name)
)
    comment '组织机构表';

• 车辆信息表

create table y_auto
(
    id                   bigint auto_increment comment 'id'
        primary key,
    license_plate_no     varchar(45)                          null comment '车牌号',
    owner                varchar(45)                          not null comment '车主姓名',
    vin_no               varchar(45)                          not null comment '车架号/车辆识别代码'
)
    comment '车辆信息表';

create index idx_vin_no
    on auto (vin_no)
    comment '车架号';

3、实际SQL

SELECT
    SUM( CASE WHEN qo.status = 10 THEN qo.total_insurance_premium ELSE 0 END ) AS totalPremium,
    SUM( CASE WHEN qo.status = 10 THEN qo.non_auto_premium ELSE 0 END) AS nonAutoPremium,
    (SELECT LEFT ( m.code, 6 ) ) AS oneLevelCode,
    SUM( CASE WHEN qo.client_type = 1 AND qo.status = 10 THEN 1 ELSE 0 END ) AS issueC,
    SUM( CASE WHEN qo.client_type = 2 AND qo.status = 10 THEN 1 ELSE 0 END ) AS issueA,
    SUM( CASE WHEN qo.status = 10 THEN 1 ELSE 0 END ) AS issueCount,
    count(DISTINCT CASE WHEN qo.status != 1 THEN a.vin_no ELSE null END) AS quoteCarNumber
FROM y_quote_order qo
         JOIN y_organization m ON m.id = qo.organization_id
         JOIN y_auto a ON a.id = qo.auto_id
WHERE qo.create_time BETWEEN '2021-05-01' AND '2022-09-14'
  AND m.level != 0
  and substr( m.code, 1, 6) not in (select mm.code from y_organization mm where m.level=1 and m.short_code in ('cctest','cheche'))
GROUP BY ( SELECT LEFT ( m.code, 6 ) )
ORDER BY totalPremium DESC;

说明：

qo.status = 10 承保完成；

qo.status = 1 暂存；

m.code 一级组织code为6位；

client_type = 2 A端；

client_type = 1 C端。

4、语法说明

• sum() 聚合求和函数；

• count（）聚合统计函数；

• distinct 去重;

• as 后面跟别名；

• between … and … 范围；

• join … on … 连表查询;

• substr（字段名 ，1, 6）截取字符串前六位；

• select left (字段名， 6) 截取字符串前六位；

• in（） 多参数；

• group by 分组；

• order by 排序 （DESC–降序， ASC-- 升序）；

• case when … then … else … end 当满足时 ，这样，不满足那样；

  5、case when … then … else … end 说明

  示例：统计A、C两端销售额：

  SELECT  
  	SUM( CASE WHEN qo.client_type = 1 THEN 1 ELSE 0 END ) AS issueC,
      SUM( CASE WHEN qo.client_type = 2 THEN 1 ELSE 0 END ) AS issueA,
  from y_quote_order qo
  where qo.status = 10;
  
  

  总结：

  常用于同一纬度不同阶段数据的统计，条件不能放在where后面是，便可使用case when … then … else … end。