首页 / 知识

关于sql server：SQL案例表达语法？

2023-04-11 19:41:00

SQL Case Expression Syntax?

SQL Case表达式的完整和正确语法是什么？

完整的语法取决于您使用的数据库引擎：

对于SQL Server：

1
2
3
4
5

CASE case-expression
WHEN when-expression-1 THEN value-1
[ WHEN when-expression-n THEN value-n ... ]
[ ELSE else-VALUE ]
END

或：

1
2
3
4
5

CASE
WHEN boolean-when-expression-1 THEN value-1
[ WHEN boolean-when-expression-n THEN value-n ... ]
[ ELSE else-VALUE ]
END

表达式等：

1
2
3
4
5
6

case-expression - something that produces a VALUE
when-expression-x - something that IS compared against the case-expression
value-1 - the RESULT OF the CASE statement IF:
the when-expression == case-expression
OR the boolean-when-expression == TRUE
boolean-when-EXP.. - something that produces a TRUE/FALSE answer

链接：CASE(Transact-SQL)

还要注意，WHEN语句的顺序很重要。您可以轻松编写多个重叠的WHEN子句，并使用第一个匹配的子句。

注意：如果未指定ELSE子句，并且找不到匹配的WHEN条件，则CASE表达式的值为NULL。

考虑到您标记了多个产品，我想说的是完全正确的语法是在ISO / ANSI SQL-92标准中找到的语法：

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

<CASE expression> ::=
<CASE abbreviation>
| <CASE specification>

<CASE abbreviation> ::=
NULLIF <LEFT paren> <VALUE expression> <comma>
<VALUE expression> <RIGHT paren>
| COALESCE <LEFT paren> <VALUE expression>
{ <comma> <VALUE expression> }... <RIGHT paren>

<CASE specification> ::=
<simple case>
| <searched case>

<simple case> ::=
CASE <CASE operand>
<simple WHEN clause>...
[ <ELSE clause> ]
END

<searched case> ::=
CASE
<searched WHEN clause>...
[ <ELSE clause> ]
END

<simple WHEN clause> ::= WHEN <WHEN operand> THEN <result>

<searched WHEN clause> ::= WHEN <SEARCH condition> THEN <result>

<ELSE clause> ::= ELSE <result>

<CASE operand> ::= <VALUE expression>

<WHEN operand> ::= <VALUE expression>

<result> ::= <RESULT expression> | NULL

<RESULT expression> ::= <VALUE expression>

语法规则

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
54
55
56
57
58
59
60
61

1) NULLIF (V1, V2) IS equivalent TO the following <CASE specification>:

CASE WHEN V1=V2 THEN NULL ELSE V1 END

2) COALESCE (V1, V2) IS equivalent TO the following <CASE specification>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

3) COALESCE (V1, V2, . . . ,n ), FOR n >= 3, IS equivalent TO the
following <CASE specification>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )
END

4) IF a <CASE specification> specifies a <simple case>, THEN let CO
be the <CASE operand>:

a) The DATA TYPE OF each <WHEN operand> WO shall be comparable
WITH the DATA TYPE OF the <CASE operand>.

b) The <CASE specification> IS equivalent TO a <searched case>
IN which each <searched WHEN clause> specifies a <SEARCH
condition> OF the form"CO=WO".

5) At least one <result> IN a <CASE specification> shall specify a
<RESULT expression>.

6) IF an <ELSE clause> IS NOT specified, THEN ELSE NULL IS im-
plicit.

7) The DATA TYPE OF a <CASE specification> IS determined BY ap-
plying Subclause 9.3,"Set operation result data types", TO the
DATA types OF ALL <RESULT expression>s IN the <CASE specifica-
tion>.

Access Rules

NONE.

General Rules

1) CASE:

a) IF a <result> specifies NULL, THEN its VALUE IS the NULL
VALUE.

b) IF a <result> specifies a <VALUE expression>, THEN its VALUE
IS the VALUE OF that <VALUE expression>.

2) CASE:

a) IF the <SEARCH condition> OF SOME <searched WHEN clause> IN
a <CASE specification> IS TRUE, THEN the VALUE OF the <CASE
specification> IS the VALUE OF the <result> OF the FIRST
(leftmost) <searched WHEN clause> whose <SEARCH condition> IS
TRUE, CAST AS the DATA TYPE OF the <CASE specification>.

b) IF no <SEARCH condition> IN a <CASE specification> IS TRUE,
THEN the VALUE OF the <CASE expression> IS the VALUE OF the
<result> OF the explicit OR implicit <ELSE clause>, CAST AS
the DATA TYPE OF the <CASE specification>.

这是PostgreSQL文档中的CASE语句示例(Postgres在此处遵循SQL标准)：

1
2
3
4
5
6

SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;

或

1
2
3
4
5
6

SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;

很显然，当您仅根据一个可能值列表检查一个字段时，第二种形式就更清洁。第一种形式允许更复杂的表达式。

Sybase具有与SQL Server相同的大小写语法：

描述

支持条件SQL表达式；可以在可以使用值表达式的任何地方使用。

句法

1
2
3
4
5

CASE
WHEN search_condition THEN expression
[WHEN search_condition THEN expression]...
[ELSE expression]
END

大小写和值语法

1
2
3
4
5

CASE expression
WHEN expression THEN expression
[WHEN expression THEN expression]...
[ELSE expression]
END

参量

案件

开始大小写表达式。

什么时候

在搜索条件或要比较的表达式之前。

搜索条件

用于设置所选结果的条件。案例表达式的搜索条件类似于where子句中的搜索条件。《 Transact-SQL用户指南》中详细介绍了搜索条件。

然后

在指定case的结果值的表达式之前。

表达

是列名，常量，函数，子查询，或由算术或按位运算符连接的列名，常量和函数的任意组合。有关表达式的更多信息，请参见中的"表达式"。

例

1
2
3
4
5
6
7
8
9
10
11
12

SELECT disaster,
CASE
WHEN disaster ="earthquake"
THEN"stand in doorway"
WHEN disaster ="nuclear apocalypse"
THEN"hide in basement"
WHEN monster ="zombie apocalypse"
THEN"hide with Chuck Norris"
ELSE
THEN"ask mom"
END
FROM endoftheworld

我在Oracle页面上挖掘了相同的内容，看起来这是相同的语法，只是描述略有不同。

链接：Oracle / PLSQL：案例声明

11g文档中的Oracle语法：

1
2
3

CASE { simple_case_expression | searched_case_expression }
[ else_clause ]
END

simple_case_expression