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

最新推荐文章于 2024-07-24 16:18:52 发布

小白de成长之路

最新推荐文章于 2024-07-24 16:18:52 发布

阅读量1.1k

点赞数 1

分类专栏：存储相关文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/Java__EE/article/details/127071118

版权

存储相关专栏收录该内容

21 篇文章 1 订阅

订阅专栏

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。