案例表达与案例陈述 [英] Case Expression vs Case Statement
问题描述
Case Expression 和MySQL中的案例声明?什么时候可以使用它们?与另一种相比,使用它们有什么好处?
What is the difference between a Case Expression and a Case Statement in MySQL? When can they be used, and what are the benefits of using one over the other?
Case Statement语法:
Case Statement syntax:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
Case Expression语法:
Case Expression syntax:
CASE
WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END
这些看起来几乎相同,但是对Case Statements的最初描述是The CASE statement for stored programs implements a complex conditional construct.
These look almost identical, but the initial description for Case Statements is that The CASE statement for stored programs implements a complex conditional construct.
那么,在存储程序中使用一个而不在普通查询中使用它的显着区别是吗?我在正在使用的查询中尝试了此方法,但失败了- sqlfiddle .如果是这种情况,为什么不只在存储程序中使用Case Expression?
So is the significant difference that one is used in stored programs and not usable in normal queries? I tried this out on a query I was playing with and it failed - sqlfiddle. If this is the case though, why not just use the Case Expression in a stored program?
还有其他语法上的差异,因为它们似乎是相同的吗?
Are there any other syntactical differences to be aware of, since they seem to be identical?
推荐答案
CASE
表达式根据某个条件求值,即,它用于根据一组条件对一组结果之一求值.
示例:
The CASE
expression evaluates to a value, i.e. it is used to evaluate to one of a set of results, based on some condition.
Example:
SELECT CASE
WHEN type = 1 THEN 'foo'
WHEN type = 2 THEN 'bar'
ELSE 'baz'
END AS name_for_numeric_type
FROM sometable`
CASE
语句根据某些条件执行一组语句中的一个.
示例:
The CASE
statement executes one of a set of statements, based on some condition.
Example:
CASE
WHEN action = 'update' THEN
UPDATE sometable SET column = value WHERE condition;
WHEN action = 'create' THEN
INSERT INTO sometable (column) VALUES (value);
END CASE
您会看到它们的相似之处,但是语句不会求值,并且可以单独使用,而表达式必须是表达式的一部分,例如查询或作业.您无法在查询中使用该语句,因为查询不能包含语句,因此仅需要对某些内容求值的表达式(在某种程度上,查询本身就是一条语句),例如SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE
没有道理.
You see how they are similar, but the statement does not evaluate to a value and can be used on its own, while the expression needs to be a part of an expression, e.g. a query or an assignment. You cannot use the statement in a query, since a query cannot contain statements, only expressions that need to evaluate to something (the query itself is a statement, in a way), e.g. SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE
makes no sense.
这篇关于案例表达与案例陈述的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!