MySQL CASE 是如何工作的?
MySQLCASE的工作方式类似于switch语句。CASE的语法如下-
案例1-比较语句
Case when anyCompareStatement then value1 when anyCompareStatement then value2 . . N else anyValue end as anyVariableName;
案例2-条件
当您只选择一列时,可以使用第二种语法。语法如下-
case yourColumnName when condition1 then result1 when condition1 then result2 . . N else anyValue end;
要理解上述概念,让我们使用select语句。
情况1
查询如下-
mysql> select -> case when 45 < 55 then '55 is greater than 45' -> when 10!=11 then '10 is not equal to 11' -> else 'Default case' -> end as Result;输出结果
+-----------------------+ | Result | +-----------------------+ | 55 is greater than 45 | +-----------------------+ 1 row in set (0.00 sec)
案例二
让我们创建一个表格来理解第二种语法。
创建表的查询如下-
mysql> create table CaseDemo -> ( -> Id int, -> Name varchar(100) -> );
使用插入命令在表中插入一些记录。查询如下-
mysql> insert into CaseDemo values(1,'John'); mysql> insert into CaseDemo values(2,'Sam');
使用select语句显示表中的所有记录。查询如下-
mysql> select *from CaseDemo;输出结果
+------+------+ | Id | Name | +------+------+ | 1 | John | | 2 | Sam | +------+------+ 2 rows in set (0.00 sec)
这是CASE的第二个查询,可用于仅处理一个值。查询如下-
mysql> select case name -> when 'John' then 'His name is John' -> when 'Sam' then 'His name is Sam' -> else -1 -> end as Result -> from CaseDemo;输出结果
+------------------+ | Result | +------------------+ | His name is John | | His name is Sam | +------------------+ 2 rows in set (0.00 sec)