需求:查询几个游戏在一周内每天的新增人数。
一、用法
case when sex = '1' then '男' when sex = '2' then '女' else '未知' end
Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
Case when 相当于一个自定义的数据透视表,group by 是行名,case when 负责列名。
二、实例
- 与GROUP BY 结合,自定义列联表统计
SELECT country, SUM( CASE WHEN sex = '1' THEN population ELSE 0 END), --男性人口 SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) --女性人口 FROM Table_A GROUP BY country;
- 增加分组条件
- 与Group by结合,自定义分组统计
/*新建一个表demo*/ CREATE TABLE demo ( type varchar(10) NULL, num int NULL) /*插入数据*/ insert into demo values ('A',10); insert into demo values ('B',10); insert into demo values ('C',10); insert into demo values ('D',10); insert into demo values ('E',10); insert into demo values ('F',10); insert into demo values ('G',10); /*利用CASE WHEN 和 GROUP BY 实现自定义分组合计*/ select case when type in('A','C','D') THEN 'ACD' when type in('F','G') THEN 'FG' else type end as types,sum(num) from demo group by case when type in('A','C','D') THEN 'ACD' when type in('F','G') THEN 'FG' else type end
-
原始表
-
select 结果
- 与distinct结合,去重分组统计
select count(distinct case when photo='1' then id else null end )photo1_count, count(distinct case when photo='2' then id else null end )photo2_count from photos
4.根据条件有选择的UPDATE
UPDATE Personnel SET salary = CASE WHEN salary >= 5000 THEN salary * 0.9 WHEN salary >= 2000 AND salary < 4600 THEN salary * 1.15 ELSE salary END;
UPDATE SomeTable SET p_key = CASE WHEN p_key = 'a' THEN 'b' WHEN p_key = 'b' THEN 'a' ELSE p_key END WHERE p_key IN ('a', 'b');
5.两个表数据是否一致的检查
--使用IN的时候 SELECT keyCol, CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) THEN 'Matched' ELSE 'Unmatched' END Label FROM tbl_A; --使用EXISTS的时候 SELECT keyCol, CASE WHEN EXISTS ( SELECT * FROM tbl_B WHERE tbl_A.keyCol = tbl_B.keyCol ) THEN 'Matched' ELSE 'Unmatched' END Label FROM tbl_A;
详情链接:https://www.cnblogs.com/aipan/p/7770611.html
参考:https://www.jianshu.com/p/f098606391ea
如果写在TP框架中,例:
M('game')
->field('rate,times,CASE WHEN times>0 && times<50 THEN 1 WHEN times>=50 && times<100 THEN 2 WHEN times>=100 && times<500 THEN 3 WHEN times>=500 && times<1000 THEN 4 ELSE 5 END AS flag')
->order('flag,rate DESC')
->select();
看图中注释理解场景