Sql 简明教程

SQL - CASE

The SQL CASE Statement

SQL CASE 语句是一个条件语句，它帮助我们基于一组条件进行决策。它评估条件集，并在满足条件时返回各自的值。

The SQL CASE statement is a conditional statement that helps us to make decisions based on a set of conditions. It evaluates the set of conditions and returns the respective values when a condition is satisfied.

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

The CASE statement works like a simplified IF-THEN-ELSE statement and allows for multiple conditions to be tested.

它经常用于基于现有列的值创建具有值的列。

It is often used to create a new column with values based on the value of an existing column.

让我们看一个简单的场景来理解这个声明。

Let us look at a simple scenario to understand this statement.

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

For e.g. when the credit limit of a customer is above '10,000', then the customer will be recognized as a 'High value customer'; when the credit limit is above '5000', then the customer will be recognized as a 'Mid value customer'; otherwise the customer will be recognized as the 'Low value customer' as shown in the table below −

Syntax

以下是 SQL CASE 语句的语法：

Following is the syntax of SQL CASE statement −

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

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

Where, condition1, condition2, etc. Are the conditional statements and statement1, statement2, etc.. are the actions to be taken when the condition is true.

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

Once the condition is met, the CASE statement will stop verifying further and it will return the result.

If none of the conditions are met (TRUE), then it returns the value mentioned in the ELSE clause.
It returns NULL if the ELSE part is not mentioned and none of the conditions are TRUE.

Example

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

Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. using the following query −

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 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(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, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将按如下方式创建：

The table will be created as follows −

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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

在以下查询中，我们正在对 CASE 语句使用多个 WHEN 和 THEN 条件以及 ELSE 子句。

In the following query, we are using multiple WHEN and THEN conditions to the CASE statement along with the ELSE clause.

如果客户的 AGE 大于 30，则返回 Gen X，否则转到进一步的 WHEN 和 THEN 条件。如果客户表中没有与查询 − 中 ELSE 部分提到的条件匹配，CASE 返回“Gen Alpha”值。

If the AGE of the customer is greater than 30, it returns Gen X otherwise moves to the further WHEN and THEN conditions. If none of the conditions is matched with the CUSTOMERS table, CASE returns the 'Gen Alpha' value as mentioned in the ELSE part of the query −

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;

Output

输出结果如下：

The output produced is as follows −

NAME

AGE

Generation

Ramesh

Gen X

Khilan

Gen Z

Kaushik

Gen Z

Chaitali

Gen Z

Hardik

Gen Y

Komal

Gen Alpha

Muffy

Gen Z

Example

让我们看另一个查询，其中我们想要为每个客户提供 25% 的增量，如果金额小于先前创建的 CUSTOMERS 表中的 4500 −

Let us take a look at another query where we want to provide a 25% increment to each customer if the amount is less than 4500 from the CUSTOMERS table previously created −

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

Output

此处，SQL 命令检查工资是否小于 4500。如果满足此条件，则新列“INCREMENT”将包含等于工资的值，增量为 25%。

Here, the SQL command checks if the salary is less than 4500. If this condition is satisfied, a new column 'INCREMENT' will contain the values that is equal to salary with 25% of increment.

由于上述查询中未提及 ELSE 部分，并且某些 CUSTOMERS 没有满足任何条件，因此返回 NULL，表明他们没有获得任何增量。

Since the ELSE part is not mentioned in the above query and none of the conditions are true for few CUSTOMERS, NULL is returned, which shows that they didn’t get any increment.

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	Hyderabad	4500.00	NULL
7	Muffy	24	Indore	10000.00	NULL

CASE Statement with ORDER BY Clause

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

We can use CASE statement with ORDER BY clause. The ORDER BY clause in SQL sorts the result in ascending (default) or descending order.

Example

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

In this query, the CASE statement is used to sort the results based on either the 'NAME' column or the 'ADDRESS' column, depending on the value of the 'NAME' column. If the 'NAME' column starts with 'K', the results are sorted by the 'NAME' column; otherwise, the results are sorted by the 'ADDRESS' column −

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

Output

通过执行上述查询获得的结果如下所示 −

The result obtained by executing the above query is as shown below −

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	Hyderabad	4500.00
4	Chaitali	25	Mumbai	6500.00

CASE Statement with GROUP BY Clause

我们还可以在 GROUP BY 子句中使用 CASE 语句。SQL 中的 GROUP BY 子句对一个或多个列中的值相同的行进行分组，并将聚合函数应用于这些行以生成汇总。

We can also use the CASE statement with GROUP BY clause. The GROUP BY clause in SQL groups the rows that have same values within one or more columns where an aggregate function is applied to produce summaries.

Example

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

In the following query we are grouping the customers based on their salaries and calculate the sum of the salary for a specified range of customer data.

如果 SALARY 中的值小于或等于 4000，则数据将被分组为“最低工资”。如果此值大于 4000 且小于或等于 6500，则将其分组为“平均工资”。所有其他值将被分组为“最高工资”。SUM 函数用于计算每组的 SALARY 总和 −

If the value in SALARY is less than or equal to 4000, the data will be grouped as 'Lowest paid'. If the value is greater than 4000 and less than or equal to 6500, it will be grouped as 'Average paid'. All other values will be grouped as 'Highest paid'. The SUM function is used to calculate the total of the SALARY for each group −

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;

Output

以下是以上查询的输出：

Following is the output of the above query −

SALARY_STATUS

Total

Lowest paid

5500.00

Average paid

11000.00

Highest paid

18500.00

CASE Statement with WHERE Clause

我们还可以在 WHERE 子句中使用 CASE 语句。WHERE 子句用于根据指定条件筛选表中的行。

We can use the CASE statement with the WHERE clause as well. The WHERE clause is used to filter the rows in a table based on a specified condition.

Example

在以下查询中，CASE 语句用于根据 CUSTOMERS 的 AGE 返回不同的名称。WHERE 子句用于根据 CUSTOMERS 的 SALARY 筛选行 −

In the following query, the CASE statement is used to return the different designations of the CUSTOMERS based on their AGE. The WHERE clause is used to filter the rows based on the SALARY of the CUSTOMERS −

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;

Output

以上查询的输出如下 −

Output of the above query is as follows −

NAME

ADDRESS

Designation

Ramesh

Ahmedabad

Senior Developer

Kaushik

Kota

Intern

Chaitali

Mumbai

Associate Engineer

Hardik

Bhopal

Associate Engineer

Komal

Hyderabad

Intern

Muffy

Indore

Intern

CASE Statement with UPDATE

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

We can use CASE statement within the UPDATE statement to perform conditional updates on data in a table.

Example

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

In the following query we are updating the salary of all the customers based on their age.

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

If the age of the customer is equal to '25', their salary will be updated to '17000'. If the age is equal to '32', it will be updated to '25000'. For the customers with other ages, salaries will be updated to '12000' −

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

Output

我们得到以下结果。我们可以观察到，这些更改已在 7 行中完成 -

We get the following result. We can observe that the changes have been done in 7 rows −

Query OK, 7 rows affected (0.02 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

我们可以使用以下查询更正 CUSTOMERS 表中完成的更改 -

We can rectify the changes done in the CUSTOMERS table using the below query −

SELECT * FROM CUSTOMERS;

该表显示如下：

The table is displayed as follows −

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	Hyderabad	12000.00
7	Muffy	24	Indore	12000.00

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

As we can see in the above table, the SALARY of all the customers has been updated corresponding to their age.

CASE Statement with INSERT

我们还可以借助 CASE 语句将数据插入 MySQL 表中。我们需要提供插入数据所用列名称和 VALUES 的 INSERT INTO 语句。

We can also insert the data into MySQL tables with the help of the CASE statement. We need to provide the INSERT INTO statement with column names and VALUES for data insertion.

Example

在这里，如果客户的年龄大于或等于 25，则工资为 23000；否则工资为 14000 -

Here, if the age of the customer is greater than or equal to 25, then the salary will be 23000; otherwise the salary will be 14000 −

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

Output

我们得到以下结果。我们可以观察到，已针对 1 行完成更改 -

We get the following result. We can observe that the change has been done in 1 row −

Query OK, 1 row affected (0.01 sec)

Verification

我们可以使用以下查询更正 CUSTOMERS 表中完成的更改 -

We can rectify the changes done in the CUSTOMERS table using the below query −

SELECT * FROM CUSTOMERS;

该表显示如下：

The table is displayed as follows −

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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00
10	Viren	28	Varanasi	23000.00