Fork me on GitHub

mysql中casewhen用法

发表于 | 分类于 | | 阅读次数:
mysql中casewhen用法

Mysql中CASE WHEN用法

1、mysql中case when 有两种用法

#####1）简单函数

1
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

#####2）搜索函数

1
CASE WHEN [expr] THEN [result1]…ELSE [default] END

2、这两种语法的区别

1）简单函数—-枚举这个字段的所有值*
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;
2）搜索函数—-搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 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;

聚合函数sum配合case when的简单函数实现行转列

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SELECT
    st.stu_id '学号',
    st.stu_name '姓名',
    sum(
        CASE co.course_name
        WHEN '大学语文' THEN
            sc.scores
        ELSE
        END
    ) '大学语文',
    sum(
        CASE co.course_name
        WHEN '新视野英语' THEN
            sc.scores
        ELSE
        END
    ) '新视野英语',
    sum(
        CASE co.course_name
        WHEN '离散数学' THEN
            sc.scores
        ELSE
        END
    ) '离散数学',
    sum(
        CASE co.course_name
        WHEN '概率论与数理统计' THEN
            sc.scores
        ELSE
        END
    ) '概率论与数理统计',
    sum(
        CASE co.course_name
        WHEN '线性代数' THEN
            sc.scores
        ELSE
        END
    ) '线性代数',
    sum(
        CASE co.course_name
        WHEN '高等数学' THEN
            sc.scores
        ELSE
        END
    ) '高等数学'
FROM
    edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
    st.stu_id
ORDER BY
    NULL;

3、case when在mysql中的位置

1）在查询条件中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;
2）在排序语句后面
1
2
3
4
5
6
7
8
SELECT d.workState from devicebase d ORDER BY 
  CASE d.workstate
  WHEN '3' THEN 0
  WHEN '2' THEN 1
  WHEN 'x' THEN 2
  WHEN 'y' THEN 3
  WHEN '1' THEN 4
  ELSE 5 END,  d.workstate;
3）在where条件后面
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
31
32
SELECT DISTINCT
	I.APPLY_ID id,
	I.SERVICE_ID serviceId,
	R.SERVICE_NAME serviceName,
	R.REAL_ADDRESS realAddress,
	X.IS_PERMANENT isPermanent,
	D.END_TIME endTime
FROM
	IRSP_EXCHANGE_APPLY X
LEFT JOIN IRSP_EXCHANGE_APPLY_DETAIL I ON X.ID = I.APPLY_ID
LEFT JOIN IRSP_SERVICE_RELEASE R ON I.SERVICE_ID = R.ID
LEFT JOIN IRSP_RESOURCES_INFO Y ON Y.ID = R.RESOURCE_ID
LEFT JOIN IRSP_EXCHANGE_APPLY_EXTEND D ON X.ID = D.APPLY_ID
WHERE
	1 = 1
AND X.APPLY_ORGAN_ID IN (
	SELECT
		ORG_ID
	FROM
		t_dmp_sys_org org
	WHERE
		org.PATH LIKE '%2c932ee657ac27d80157ac4421de0001%'
	OR org.ORG_ID = '2c932ee657ac27d80157ac4421de0001'
)
AND X.FLOW_STATUS = '2' -- and ( r.`STATUS` = '1' and d.END_TIME < DATE_FORMAT(NOW(),'%Y-%m-%d') )
AND (
	CASE
	WHEN (r.`STATUS` = '1' AND r.SERVICE_STATUS = '1') 		THEN d.END_TIME < DATE_FORMAT(NOW(),'%Y-%m-%d')
	WHEN r.`STATUS` = '1' THEN r.SERVICE_STATUS = '0'w
	WHEN r.`STATUS`='0'  THEN r.SERVICE_STATUS='1' END)
	ORDER BY
		X.CREATE_TIME DESC