2.case when then 语句

Posted on Edited on In

 

Case语句具有两种格式：

  • 简单Case函数
  • 搜索Case函数

1.简单Case函数

格式：

1
2
3
4
5
6
CASE [field]
    WHEN [value_1] THEN [value_a]
    WHEN [value_2] THEN [value_b]
    WHEN [value_3] THEN [value_c]
    ELSE [value_default]
END

示例1：

1
2
3
4
5
6
7
8
9
10
SELECT commodity_name,
    (
        CASE standard_code
            WHEN 'code1' THEN 'standard 1'
            WHEN 'code2' THEN 'standard 2'
            WHEN 'code3' THEN 'standard 3'
            ELSE 'other'
        END
    ) as standard_name
from commodity;

2.搜索Case函数

格式：

1
2
3
4
5
6
CASE
    WHEN [condition_1] THEN [value_1]
    WHEN [condition_2] THEN [value_2]
    WHEN [condition_3] THEN [value_3]
    ELSE [value_default]
END

示例1：

1
2
3
4
5
6
7
8
9
10
SELECT commodity_name,
    (
        CASE
            WHEN standard_code = 'code1' THEN 'standard 1'
            WHEN standard_code = 'code2' THEN 'standard 2'
            WHEN standard_code = 'code3' THEN 'standard 3'
            ELSE 'other'
        END
    ) as standard_name
from commodity;

示例2：

1
2
3
4
5
6
7
8
9
SELECT team_name,
(
	CASE
		WHEN score < 60 THEN 'low'
		WHEN score between 60 and 80 THEN 'medium'
		WHEN score > 80 THEN 'high'
	END
) as result
from team_record;