exist,基本上可以用in代替，后面通常是子查询,子查询通常是select *

1
2
3
select u.name,u.class from user as u where exists (
	select * from class as c where u.class=c.id and u.class=1
)

case表达式

1
2
3
4
5
6
7
8
9
10
11
12
13
返回的数据类型需一致
-- concat字符连接
select name, 
    case when class = 1 then concat("A", class) 
    when class = 2 then concat('B',class)
    else NULL end。-- 若不写else默认返回NULL
    as class_t
from user;
-- 写法2
case name
    when 'xxh' then 'ok'
	when 'xjy' then 'no'
else 'other' end

通过case为money设置等级以及统计数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select 
	case when money <= 1000 then '01'
	when money <= 2000 then '02'
	when money <= 3000 then '03'
	else '04' end as money_level,
	count(*)
from user group by
	case when money <= 1000 then '01'
	when money <= 2000 then '02'
	when money <= 3000 then '03'
	else '04' end;
-- 输出
+-------------+----------+
| money_level | count(*) |
+-------------+----------+
| 01          |        1 |
| 02          |        3 |
| 03          |        2 |
| 04          |        1 |
+-------------+----------+
-- group by之后使用select的别名也可以正确执行，但违反了sql规范(select在group by后执行)
select  case when money <= 1000 then '01' when money <= 2000 then '02' when money <= 3000 then '03' else '04' end as money_level, count(*) from user group by money_level;

聚合函数中使用case

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 统计每个班男女生总money
select class,
	sum(case when gender=1 then money else 0 end) as male_money,
	sum(case when gender=2 then money else 0 end) as female_money
from user group by class;
-- 查询结果
+-------+------------+--------------+
| class | male_money | female_money |
+-------+------------+--------------+
|     1 |    1000.00 |      6500.00 |
|     2 |    4499.00 |      1500.00 |
|     3 |    2000.00 |         0.00 |
+-------+------------+--------------+

update语句中使用case

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 工资为30w以上的降薪10%，工资为25w-28w的加薪20%
-- 若写两条update则不正确
update salarys set salary = salary * 1.1 where salary >= 300000;
update salarys set salary = salary * 0.8 where salary >= 250000 and salary <= 280000;
-- 30w的员工降薪后又执行了加薪操作，可使用case解决
update salarys set salary = 
case when salary >= 300000 then salary * 1.1
	 when salary >= 250000 and salary <= 280000 then salary * 0.8
else salary end;
-- 若交换两条记录的值，若不使用case需要借助中间变量
update user set key = 'c' where key = 'a';
update user set key = 'a' where key = 'b';
update user set key = 'b' where key = 'c';
-- 若使用case只需执行一次
update user set key = 
case when key = 'a' then 'b'
	 when key = 'b' then 'a'
else key end where key in ('a', 'b');

case和in、exist的结合使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 查看学生某个月份是否有课
select name,
case when class in (
	select id from class where open_time like "2020-02-%") then "有课"
else '没课' end as "二月",
case when class in (
	select id from class where open_time like "2020-03-%") then "有课"
else "没课" end as "三月" 
from user;
-- 结果
+------+--------+--------+
| name | 二月   | 三月   |
+------+--------+--------+
| xxh  | 有课   | 没课   |
| xxh2 | 有课   | 没课   |
| xxh3 | 没课   | 有课   |
| xxh4 | 有课   | 没课   |
| xxh5 | 有课   | 没课   |
| xxh6 | 有课   | 没课   |
| 111  | 有课   | 没课   |
+------+--------+--------+
-- 使用exists，结果一致
select name,
case when exists (
	select id from class where open_time like "2020-02-%") then "有课"
else '没课' end as "二月",
case when exists (
	select id from class where open_time like "2020-03-%") then "有课"
else "没课" end as "三月" 
from user;

使用case对多列求最大值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 表结构如下
+------+------+------+------+
| key  | x    | y    | z    |
+------+------+------+------+
| A    |    1 |    2 |    3 |
| B    |    5 |    5 |    2 |
| C    |    4 |    7 |    1 |
| D    |    3 |    3 |    8 |
+------+------+------+------+
-- 先求出x、y的最大值
select case when x > y then x else y end from greatests;
-- 再和z比较
select 
	case when z > (case when x > y then x else y end) then z
 	else (case when x > y then x else y end)  end
from greatests; 
-- 方法二，使用内置函数
select GREATEST(GREATEST(x,y), z) from greatests;

矩阵转置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 原数据
+----------+------+------+
| province | sex  | num  |
+----------+------+------+
| 山东     |    1 |   60 |
| 山东     |    2 |   40 |
| 上海     |    1 |  100 |
| 上海     |    2 |  100 |
| 杭州     |    1 |  100 |
| 杭州     |    2 |   50 |
+----------+------+------+
select 
  case when sex=1 then '男' else '女' end as '性别',
  sum(case when province='山东' then num else 0 end) as '山东',
  sum(case when province='上海' then num else 0 end) as '上海',
  sum(case when province='杭州' then num else 0 end) as '杭州'
from province_people group by sex;
-- 结果
+--------+--------+--------+--------+
| 性别   | 山东   | 上海   | 杭州   |
+--------+--------+--------+--------+
| 男     |     60 |    100 |    100 |
| 女     |     40 |    100 |     50 |
+--------+--------+--------+--------+

按照特定顺序输出，不输出a, b, c, d, 输出b, a, d, c

1
2
3
4
5
6
7
select `key` from greatests order by 
  case `key` 
	when 'B' then 1
	when 'A' then 2
	when 'D' then 3
	when 'C' then 4
  end;