Mysql 如何优雅的生成一些普通报表数据呢？mysql 的 case when 用法 (新手可看，老鸟勿喷～)

2020-06-10 | 0 浏览 | 0 评论

背景

工作中，难免会遇到一些数据报表查询、报表邮件发送等等，但是大部分新人 SQL 语法用的并不会很多，很多会停留在学习时候的基础阶段，毕竟工作中，大部分时候是写代码，而非写 SQL 嘛！

一个常用的报表查询语句

select 
 FROM_UNIXTIME( `create_time`, '%Y-%m-%d' ) AS date
 ,sum(case productid when 'content_monthly' then 1 else 0 end) cv1
 ,sum(case productid when 'vip_1_month' then 1 else 0 end) v1
 ,sum(case productid when 'vip_3_month' then 1 else 0 end) v3
 ,sum(case productid when 'vip_12_month' then 1 else 0 end) v12
 ,sum(case productid when 'content_monthly' then round(amount/100,2) else 0 end) cv1a
 ,sum(case productid when 'vip_1_month' then round(amount/100,2)  else 0 end) v1a
 ,sum(case productid when 'vip_3_month' then round(amount/100,2)  else 0 end) v3a
 ,sum(case productid when 'vip_12_month' then round(amount/100,2)  else 0 end) v12a
FROM `order` 
WHERE `status` = 1 
GROUP BY date
ORDER BY date desc;

查询结果

当然由于库里没啥数据，结果集并不漂亮哈！

SQL 关键语法

我想很多新人再做一些报表数据时，可能普遍做法就是 group by，然后用程序去循环数据生成想要的报表结果，但是如果简单用一些语法，你会发现基础报表数据还是蛮简单的～

sum：我想新人也是必会的技能
case when then else:这是今天简单讲解的语法，也许很多新人并不会用

MySQL 的 case when 的语法有两种：

简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END

简单函数

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END：枚举这个字段所有可能的值*

SELECT
    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;

搜索函数

CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他 cas 被忽略

# when 表达式中可以使用 and 连接条件
SELECT
    NAME '英雄',
    age '年龄',
    CASE
        WHEN age < 18 THEN
            '少年'
        WHEN age < 30 THEN
            '青年'
        WHEN age >= 30
        AND age < 50 THEN
            '中年'
        ELSE
            '老年'
    END '状态'
FROM
    user_info;

再来一些例子

case when 自定义排序时的使用

根据 case when 新的 sort 字段排序

case 
    when t2.status = 4
    and t2.expire_time>UNIX_TIMESTAMP()
    and t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 day)) then 4
        when `status` = 2  then 3
        when `status` = 3 then 2
        when t2.status = 4
        and t2.expire_time>UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 day))
        and t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 1 year)) then 1
    else 0
    end sort     
    
     $query->orderBy('sort desc ,t2.expire_time desc,t2.created_at desc');

当 colume 与 condition 条件相等时结果为 result

case colume 
    when condition then result
    when condition then result
    when condition then result
else result
end

当满足某一条件时，执行某一 result

case  
    when condition then result
    when condition then result
    when condition then result
else result
end

当满足某一条件时，执行某一 result，把该结果赋值到 new_column_name 字段中

case  
    when condition then result
    when condition then result
    when condition then result
else result
end new_column_name

case when 用在 select 语句中，新的字段 new_column_name 可以用来排序，但是不能用在 where 中

🐶 你走，我不送你。你来，风雨无阻，我去接你。