mysql case表达式 查询时将状态改为相对应的文字

参考：

https://blog.csdn.net/qq_58510930/article/details/125787968

https://blog.csdn.net/wen_3370/article/details/125431274

简单CASE表达式

SELECT CASE 列名称
    WHEN 匹配字符 THEN 转换字符
    WHEN 匹配字符 THEN 转换字符
ELSE 转换字符 END AS 转换的表名
FROM 表名

example

SELECT CASE s_sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
ELSE '中间人' END AS 性别
FROM student

#假设100以下为第一级别，100-200为第二级别，200-300为三级别，300以上为四级别
SELECT CASE
           WHEN population < 100 THEN '01'
           WHEN population >= 100 AND population < 200 THEN '02'
           WHEN population >= 200 AND population < 300 THEN '03'
           WHEN population >= 300 then '04'
else '不清楚' end as 级别编号,count(*) as 数量
from poptbl
group by 级别编号
order by 级别编号

# CASE WHEN sex='1' THEN population ELSE 0 END表示当sex=1的时候，取出population，否则为0
SELECT pref_name,
       SUM(CASE WHEN sex='1' THEN population ELSE 0 END ) AS '男',
       SUM(CASE WHEN sex='2' THEN population ELSE 0 END ) AS '女'
       FROM poptbl2
GROUP BY pref_name

select * from (select date(create_time) as day, (case s.spider_status when -1 then '失败' when 0 then '成功' end) as status, count(1) as count from spider_status s where  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)  group by day desc, status desc ) k order by day
​

​
CASE loan_status
            WHEN 1 THEN '待放款'
            WHEN 2 THEN '放款失败'
            WHEN 3 THEN '已放款'
            WHEN 4 THEN '逾期'
            WHEN 5 THEN '已结清'
            WHEN 6 THEN '己退货取消放款'
            WHEN 7 THEN '退货退款'
            WHEN 8 THEN '转支付结清'
        END
          AS "loanStatus",