mysql替换if_mysql 查询 中 替换查询内容的两个用法（IF和case when else）

1、-- if 只能替换一个值

select if(operation_type=3 ,‘赎回’,operation_type) as 类型,count() as 数量 from mine_pool_order where DATE_SUB(CURDATE(), INTERVAL 7 DAY)+4 <= created_time and pool_id=12

GROUP BY operation_type

order by 数量 desc

2、-- CASE—when—else查询数据查询出的内容进行替换

select CASE operation_type

WHEN 1 THEN

‘锁仓’

WHEN 2 THEN

‘提取’

WHEN 3 THEN

‘赎回’

WHEN 4 THEN

‘质押’

ELSE

‘冻结’

END operation_type ,count() as 数量 from mine_pool_order

where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= created_time

and user_id not in(142,143,141,140)

GROUP BY operation_type

order by 数量 desc

3、-- 修改成如下这种形式可以 as 重命名字段值 ，上面不行。

select (CASE WHEN operation_type=1 THEN ‘锁仓’ WHEN operation_type= 2 THEN ‘提取’ WHEN operation_type= 3 THEN ‘赎回’ WHEN operation_type= 4 THEN ‘质押’ ELSE ‘冻结’ END )as 类型 ,count() as 数量 from mine_pool_order

where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= created_time

and user_id not in(142,143,141,140)

GROUP BY operation_type

order by 数量 desc

4、-- 也可以不用else ，一直when下去

select (CASE WHEN operation_type=1 THEN ‘锁仓’ WHEN operation_type= 2 THEN ‘提取’ WHEN operation_type= 3 THEN ‘赎回’ WHEN operation_type= 4 THEN ‘质押’ WHEN operation_type= 5 THEN ‘冻结’ END )as 类型 ,count() as 数量 from mine_pool_order

where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= created_time

and user_id not in(142,143,141,140)

GROUP BY operation_type

order by 数量 desc

5、-- 查询每天每、个类型分别分组

select DATE_FORMAT(created_time,’%Y-%m-%d’) as 时间,(CASE WHEN operation_type=1 THEN ‘锁仓’ WHEN operation_type= 2 THEN ‘提取’ WHEN operation_type= 3 THEN ‘赎回’ WHEN operation_type= 4 THEN ‘质押’ WHEN operation_type= 5 THEN ‘冻结’ END )as 类型,count(*) as 数量 from mine_pool_order

where DATE_SUB(CURDATE(), INTERVAL 7 DAY)+1 <= created_time – +1是加上今天总共7天

and user_id not in(142,143,141,140)-- 去掉模拟账户

GROUP BY 时间,operation_type

order by 时间,数量 desc;