sql 语句case_了解SQL CASE语句

sql 语句case

Hey, folks! Hope you all are doing well. In this article, we will be understanding the working of SQL CASE Statement.

嘿伙计！ 希望大家一切都好。 在本文中，我们将了解SQL CASE语句的工作。

---

---

什么是SQL CASE语句？ (What is SQL CASE Statement?)

Every programming language, such as Java, Python, C, C++, etc, contains various forms of conditional-statements to validate and choose the actions depending upon the conditions mentioned.

每种编程语言（例如Java，Python，C，C ++等）都包含各种形式的条件语句，以根据提到的条件来验证和选择操作。

Let us understand this through an example.

让我们通过一个例子来理解这一点。

In a retail store, various customers visit to buy different necessary food items and accessories. The retailer decides that he wants a software that would work on the below condition in an automated manner–

在零售商店中，各种顾客来购买不同的必需食品和配件。 零售商决定他要一种可以在以下条件下以自动化方式运行的软件–

“Every customer will be granted a discount of 5% on the bought items if and only if the total cost of the items they are about to buy, exceeds 20$.”

“只有并且当他们要购买的商品的总成本超过20美元时，每个客户才能获得所购买商品的5％折扣。”

So, the programmer or the developer would use the Case statement according to the preferred language and set the condition that if the total price exceeds 20$, then apply 5% discount on the bought items.

因此，程序员或开发人员将根据首选语言使用Case语句，并设置条件，如果总价超过20美元，则对所购买的物品应用5％的折扣。

So, from the above example, you all must have got an idea about Case statement.

因此，从上面的示例中，您必须都对Case语句有所了解。

Yes, you have guessed it right!

是的，您猜对了！

SQL CASE statement helps us apply and set some conditions on the data and then provide the results/actions based upon the fulfillment of the condition. The CASE statement returns the value or performs the action that is mentioned against the fulfillment of the particular condition/conditions.

SQL CASE语句帮助我们在数据上应用和设置一些条件，然后根据条件的满足提供结果/操作。 CASE语句返回该值或针对特定条件的满足执行所提及的操作。

Now, let us understand the working of SQL CASE statement in the below section.

现在，让我们在下面的部分中了解SQL CASE语句的工作方式。

---

---

CASE语句的语法 (Syntax of CASE statement)

SQL CASE statement returns a particular value if the first condition is met, else it checks for the next condition and the process continues.

如果满足第一个条件， SQL CASE statement将返回特定值，否则它将检查下一个条件，然后继续执行该过程。

Syntax:

句法：

CASE
    WHEN condition-1 THEN result-1
    WHEN condition-2 THEN result-2
    .
    .
    .
    WHEN condition-N THEN result-N
    ELSE default-result
END;

• If no condition is true, the CASE statement returns the value specified in the ELSE portion.
  如果没有条件为真，则CASE语句返回ELSE部分中指定的值。

• Moreover, if we do not provide any ELSE condition and if none of the conditions is TRUE, then the CASE statement returns a NULL value.
  此外，如果我们不提供任何ELSE条件，并且条件都不为TRUE，则CASE语句将返回NULL值。

---

---

通过示例实现SQL Case语句 (Implementing SQL Case Statement through examples)

Here, we will implement the SQL Case statement with various SQL queries and clauses.

在这里，我们将使用各种SQL查询和子句来实现SQL Case语句。

Let us first create a table using SQL Create query and insert values into the table through SQL Insert query.

让我们首先使用SQL Create查询创建一个表，然后通过SQL Insert查询将值插入该表中。

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");

Thus, we have created a table — ‘Info’ and added values to the columns created as above.

因此，我们创建了一个表“ Info”，并将值添加到如上所述创建的列中。

---

---

带CASE语句SQL SELECT查询 (SQL SELECT query with CASE statement)

We execute the SQL SELECT query to display the data values depending upon certain conditions stated in the CASE statement as mentioned in the below piece of code.

我们执行SQL SELECT查询，以根据CASE语句中所述的某些条件显示数据值，如以下代码中所述。

Example:

例：

SELECT city, Cost,
CASE
    WHEN Cost < 20 THEN 'Displaying price less than 20.'
    ELSE 'Price rates above 20'
END AS Price_Rate
FROM Info;

As seen above, when we execute the query, it will display the column values of ‘city’ and ‘Cost’. It creates a new column ‘Price_Rate’ to display the results of the conditions met.

如上所示，当我们执行查询时，它将显示“ city”和“ Cost”的列值。 它创建一个新列“ Price_Rate”以显示满足条件的结果。

Output:

输出：

Case Statement Example 案例陈述示例

---

---

使用CASE语句SQL Update查询 (SQL Update query with CASE statement)

Now, let us try executing SQL Update query with CASE statement.

现在，让我们尝试使用CASE语句执行SQL Update查询 。

Example:

例：

In the below example, we have passed an UPDATE query to alter the names of the cities passed in the CASE condition to a specified value.

在下面的示例中，我们传递了UPDATE查询，以将在CASE条件下传递的城市的名称更改为指定的值。

UPDATE Info 
SET city  = CASE city
 WHEN 'Pune' THEN 'POONA' 
 WHEN 'USA' THEN 'UNITED NAIONS' 
 ELSE  'INDIA'
END;

Output:

输出：

Case Statement Example 2 案例陈述示例2

After having clubbed CASE statement with the SQL queries, now let us try to execute it with some SQL Clauses.

在将CASE语句与SQL查询结合在一起之后，现在让我们尝试使用一些SQL子句执行它。

---

---

带有CASE语句SQL ORDER BY子句 (SQL ORDER BY clause with CASE statement)

Here, we have executed ORDER BY clause with CASE statement.

在这里，我们使用CASE语句执行了ORDER BY子句 。

We select all the data values from the table – ‘Info’ using the SELECT query. Further, we add an ORDER BY clause with a CASE statement that states the following conditions.

我们使用SELECT查询从表中选择所有数据值-“信息”。 此外，我们添加了带有CASE语句的ORDER BY子句，该子句声明了以下条件。

• Display the data values in an ascending order of ‘Cost’ only if the city value is ‘Pune’.
  仅当城市值为“浦那”时，才以“成本”的升序显示数据值。

Example:

例：

Select *
 from Info
 ORDER BY  CASE city
WHEN 'Pune' THEN Cost End;

Output:

输出：

Case Statement Example 3 案例陈述示例3

---

---

结论 (Conclusion)

That’s all for this topic. I recommend the readers to implement CASE Statement with GROUP BY clause, HAVING clause, DELETE query, etc.

这就是本主题的全部内容。 我建议读者使用GROUP BY子句 ， HAVING子句 ， DELETE查询等实现CASE语句。

Feel free to comment below, in case you come across any doubt.

如果您有任何疑问，请在下面发表评论。

For more such posts related to SQL, please do visit SQL JournalDev.

有关与SQL有关的更多此类帖子，请访问SQL JournalDev 。

---

---

参考资料 (References)

• SQL CASE STATEMENT — DOCUMENTATION
  SQL案例声明—文档

翻译自: https://www.journaldev.com/41782/sql-case-statement

sql 语句case