SQL CASE语句

SQL CASE语句是一种条件语句，可以根据特定的条件做决策。它评估一组条件并返回满足条件时的相应结果。

CASE语句类似于简化版的IF-THEN-ELSE语句，并允许测试多个条件。

它以关键字CASE开头，后面跟着多个条件语句。每个条件语句由至少一个WHEN和THEN语句对组成，其中WHEN指定条件语句，THEN指定要执行的动作。

它经常用于根据现有列的值创建一个新列。

让我们看一个简单的场景来理解这个语句。假设我们正在分析数据，我们经常希望根据某些条件定义规则。

例如，当客户的信用额度超过’10,000’时，该客户将被认为是一个“高价值客户”；当信用额度超过’5000’时，该客户将被认为是一个“中价值客户”；否则，该客户将被认为是“低价值客户”，如下表所示：

SQL CASE语句

语法

下面是SQL CASE语句的语法：

CASE
   WHEN condition1 THEN statement1,
   WHEN condition2 THEN statement2,
   WHEN condition THEN statementN
   ELSE result
END;

其中， condition1，condition2， 等等是条件语句， statement1，statement2， 等等是当条件为真时要执行的操作。

一旦条件满足，CASE语句将停止进一步验证并返回结果。

如果没有条件满足（为真），则返回 ELSE 子句中指定的值。
如果未提及ELSE部分，并且没有条件为TRUE，返回NULL。

示例

假设我们创建了一个名为 CUSTOMERS 的表，其中包含顾客的个人详细信息，包括他们的姓名、年龄、地址和薪水等，使用以下查询创建表－

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在，使用INSERT语句将值插入到这个表中，如下所示−

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

表格将按照以下方式创建 −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

在下面的查询中，我们使用多个WHEN和THEN条件来对CASE语句进行操作，同时加上ELSE子句。

如果客户的年龄大于30岁，它返回“Gen X”，否则继续执行进一步的WHEN和THEN条件。如果没有一个条件与CUSTOMERS表匹配，CASE将返回查询中ELSE部分中提到的“Gen Alpha”值，如下所示：

SELECT NAME, AGE,
CASE 
WHEN AGE > 30 THEN 'Gen X'
WHEN AGE > 25 THEN 'Gen Y'
WHEN AGE > 22 THEN 'Gen Z'
ELSE 'Gen Alpha' 
END AS Generation
FROM CUSTOMERS

输出

生成的输出如下：

+----------+-----+------------+
| NAME     | AGE | Generation |
+----------+-----+------------+
| Ramesh   |  32 | Gen X      |
| Khilan   |  25 | Gen Z      |
| kaushik  |  23 | Gen Z      |
| Chaitali |  25 | Gen Z      |
| Hardik   |  27 | Gen Y      |
| Komal    |  22 | Gen Alpha  |
| Muffy    |  24 | Gen Z      |
+----------+-----+------------+

示例

我们来看另一个查询，如果金额小于4500，则对每个客户提供25％的增量。之前创建的CUSTOMERS表如下：

SELECT *,
CASE WHEN SALARY < 4500 THEN (SALARY + SALARY * 25/100)
END AS INCREMENT
FROM CUSTOMERS;

输出

在这里，SQL命令检查薪水是否小于4500。如果满足条件，一个新的列’INCREMENT’将包含等于薪水增长25%的值。

由于上述查询中未提及ELSE部分，并且对于一些客户，没有满足任何条件，则返回NULL，表示他们没有获得任何加薪，如下所示-

+----+----------+-----+-----------+----------+-------------+
| ID | NAME     | AGE | ADDRESS   | SALARY   | INCREMENT   |
+----+----------+-----+-----------+----------+-------------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 2500.000000 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 1875.000000 |
|  3 | kaushik  |  23 | Kota      |  2000.00 | 2500.000000 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |        NULL |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |        NULL |
|  6 | Komal    |  22 | MP        |  4500.00 |        NULL |
|  7 | Muffy    |  24 | Indore    | 10000.00 |        NULL |
+----+----------+-----+-----------+----------+-------------+

CASE语句与ORDER BY子句

我们可以使用CASE语句与ORDER BY子句。SQL中的ORDER BY子句按升序（默认）或降序对结果进行排序。

示例

在此查询中，CASE语句用于根据‘NAME’列或‘ADDRESS’列的值对结果进行排序。如果‘NAME’列以’K’开头，则结果按‘NAME’列排序；否则，结果按‘ADDRESS’列排序−

SELECT * FROM CUSTOMERS
ORDER BY
(CASE
    WHEN NAME LIKE 'k%' THEN NAME
    ELSE ADDRESS
END);

输出

执行上述查询所获得的结果如下所示：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

带有GROUP BY子句的CASE语句

我们还可以将CASE语句与GROUP BY子句一起使用。SQL中的GROUP BY子句将具有相同值的行分组到一个或多个列中，其中应用了聚合函数以产生摘要。

示例

在以下查询中，我们尝试根据他们的工资分组客户，并计算指定范围的客户数据的工资总和。

如果“SALARY”字段的值小于或等于4000，则将数据分组为“最低薪资”。如果值大于4000且小于或等于6500，则将其分组为“平均薪资”。所有其他值将分组为“最高薪资”。使用SUM函数计算每个组的SALARY总和-

SELECT 
   CASE 
      WHEN SALARY <= 4000 THEN 'Lowest paid'
      WHEN SALARY > 4000 AND SALARY <= 6500 THEN 'Average paid'
   ELSE 'Highest paid' 
      END AS SALARY_STATUS,
   SUM(SALARY) AS Total
   FROM CUSTOMERS
   GROUP BY 
   CASE 
      WHEN SALARY <= 4000 THEN 'Lowest paid'
      WHEN SALARY > 4000 AND SALARY <= 6500 THEN 'Average paid'
   ELSE 'Highest paid'
END

输出

以下是上述查询的输出结果 –

+---------------+----------+
| SALARY_STATUS | Total    |
+---------------+----------+
| Lowest paid   |  5500.00 |
| Average paid  | 11000.00 |
| Highest paid  | 18500.00 |
+---------------+----------+

CASE语句与WHERE子句

我们也可以将CASE语句与WHERE子句一起使用。WHERE子句用于根据指定的条件过滤表中的行。

示例

在以下查询中，CASE语句根据客户的年龄返回不同的职位名称。WHERE子句根据客户的薪资过滤行 –

SELECT NAME, ADDRESS, 
   CASE 
      WHEN AGE < 25 THEN 'Intern'
      WHEN AGE >= 25 and AGE <= 27 THEN 'Associate Engineer'
      ELSE 'Senior Developer'
   END as Designation
FROM CUSTOMERS
WHERE SALARY >= 2000

输出

上述查询的输出如下-

+----------+-----------+--------------------+
| NAME     | ADDRESS   | Designation        |
+----------+-----------+--------------------+
| Ramesh   | Ahmedabad | Senior Developer   |
| kaushik  | Kota      | Intern             |
| Chaitali | Mumbai    | Associate Engineer |
| Hardik   | Bhopal    | Associate Engineer |
| Komal    | MP        | Intern             |
| Muffy    | Indore    | Intern             |
+----------+-----------+--------------------+

CASE语句中带有UPDATE语句

我们可以在UPDATE语句中使用CASE语句来对表中的数据进行条件更新。

示例

在以下查询中，我们尝试根据客户的年龄更新所有客户的工资。

如果客户的年龄等于’25’，则工资将更新为’17000’。如果年龄等于’32’，则更新为’25000’。对于其他年龄的客户，工资将更新为’12000’−

UPDATE CUSTOMERS
SET SALARY= 
CASE AGE
WHEN 25 THEN 17000
WHEN 32 THEN 25000
ELSE 12000
END;

输出

我们得到以下结果。我们可以观察到对7行进行了更改 –

(7 rows affected)

验证

我们可以使用以下查询来纠正在CUSTOMERS表中所做的更改-

Select * from customers;

该表的显示如下－

+----+----------+-----+-----------+-----------+
| ID | NAME     | AGE | ADDRESS   | SALARY    |
+----+----------+-----+-----------+-----------+
|  1 | Ramesh   |  32 | Ahmedabad |  25000.00 |
|  2 | Khilan   |  25 | Delhi     |  17000.00 |
|  3 | kaushik  |  23 | Kota      |  12000.00 |
|  4 | Chaitali |  25 | Mumbai    |  17000.00 |
|  5 | Hardik   |  27 | Bhopal    |  12000.00 |
|  6 | Komal    |  22 | MP        |  12000.00 |
|  7 | Muffy    |  24 | Indore    |  12000.00 |
+----+----------+-----+-----------+-----------+

正如我们在上表中看到的，所有客户的薪水已根据他们的年龄进行了更新。

带有INSERT语句的CASE语句

我们也可以通过CASE语句将数据插入SQL表中。在SQL中，我们需要指定变量来存储列值，根据这些值我们需要在CASE语句中插入数据。

示例

在这里，我们声明了变量“AGE”，其值为“28”，用于存储客户的年龄。然后，“AGE”变量作为客户薪水的条件。如果客户的年龄大于或等于25岁，则薪水为23000；否则薪水为14000-

Declare @AGE INT
Set @AGE = 28
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (10, 'Viren', 28, 'Varanasi', 
CASE 
   WHEN @AGE >= 25 THEN 23000
   ELSE 14000
END);

输出

我们得到以下结果。我们可以观察到变化已经在1行中完成−

(1 row affected)

验证

我们可以使用以下查询来纠正在CUSTOMERS表中进行的更改-

Select * from customers;

表格显示如下：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
| 10 | Viren    |  28 | Varanasi  | 23000.00 |
+----+----------+-----+-----------+----------+