了解SQL Server CASE语句

SQL Server CASE statement is equivalent to the IF-THEN statement in Excel.

SQL Server CASE语句等效于Excel中的IF-THEN语句 。

The CASE statement is used to implement the logic where you want to set the value of one column depending upon the values in other columns.

CASE语句用于实现要根据另一列中的值设置一列的值的逻辑。

The SQL Server CASE Statement consists of at least one pair of WHEN and THEN statements. The WHEN statement specifies the condition to be tested. The THEN statement specifies the action if the WHEN condition returns TRUE.

SQL Server CASE语句至少由一对WHEN和THEN语句组成。 WHEN语句指定要测试的条件。 如果WHEN条件返回TRUE，则THEN语句指定操作。

The ELSE statement is optional and executes when none of the WHEN conditions return true. The CASE statement ends with an END keyword.

ELSE语句是可选的，并且在WHEN条件均未返回true时执行。 CASE语句以END关键字结尾。

In this article, we will take a look at a number of different examples of the CASE statement. But before we do that, we’ll create some dummy data to work with.

在本文中，我们将介绍CASE语句的许多不同示例。 但是在此之前，我们将创建一些虚拟数据以供使用。

创建虚拟数据 (Creating dummy data)

Execute the following script to create the dummy data:

执行以下脚本来创建虚拟数据：

CREATE Database ShowRoom;
GO
USE ShowRoom;
 
CREATE TABLE Cars
(
    id INT,
    name VARCHAR(50) NOT NULL,
    company VARCHAR(50) NOT NULL,
    power INT NOT NULL,
    color VARCHAR(50) NOT NULL,
    model INT NOT NULL,
    condition VARCHAR(50) NOT NULL
 )

The script above has created a dummy database called ShowRoom with one Table in it called Cars. The Cars table has seven columns: id, name, company, power, color, model, and condition.

上面的脚本创建了一个名为ShowRoom的虚拟数据库，其中包含一个名为Cars的表。 “汽车”表具有七列：id，名称，公司，权力，颜色，型号和条件。

Now let’s insert some dummy data into the Cars table. Execute the following script:

现在，让我们将一些虚拟数据插入Cars表。 执行以下脚本：

USE ShowRoom
 
INSERT INTO Cars
 
VALUES
(1, 'Corrolla', 'Toyota', 1800, 'red', 1995, 'X'),
(2, 'City', 'Honda', 1500 , 'black', 2015, 'X'),
(3, 'C200', 'Mercedez', 2000 , 'white', 1992, 'X'),
(4, 'Vitz', 'Toyota', 1300 , 'blue', 2007, 'X'),
(5, 'Baleno', 'Suzuki', 1500 , 'white', 2012, 'X'),
(6, 'C500', 'Mercedez', 5000 , 'grey', 1994, 'X'),
(7, '800', 'BMW', 8000 , 'blue', 2016, 'X'),
(8, 'Mustang', 'Ford', 5000 , 'red', 1997, 'X'),
(9, '208', 'Peugeot', 5400, 'black', 1999, 'X'),
(10, 'Prius', 'Toyota', 3200 , 'red', 2003, 'X')

Let’s check how our dataset looks, execute the following script:

让我们检查数据集的外观，执行以下脚本：

SELECT * FROM Cars

The output looks like this:

输出看起来像这样：

You can see that the condition column contains an X in each row at the moment. We will set the value of the condition column, depending on the model column, using the CASE statement so that you can see clearly what is going on.

您可以看到条件列目前在每行中都包含一个X。 我们将根据模型列使用CASE语句设置条件列的值，以便您可以清楚地看到发生了什么。

SQL Server CASE语句语法 (SQL Server CASE statement syntax)

The syntax of the CASE statement is pretty straight forward:

CASE语句的语法非常简单：

SELECT column1,
             column2,
               CASE WHEN CONDITION THEN 'Value1'
               ELSE 'Value2' END AS columnX
  FROM Cars

The CASE statement has to be included inside the SELECT Statement. It starts with the CASE keyword followed by the WHEN keyword and then the CONDITION.

CASE语句必须包含在SELECT语句中。 它从CASE关键字开始，然后是WHEN关键字，然后是CONDITION。

The condition can be any valid SQL Server expression which returns a boolean value. For instance, the condition can be model > 2000, the THEN clause is used after the CONDITION. If the CONDITION returns true the value that follows the THEN clause is stored in columnX. Else, the value after the ELSE clause, will also be stored in columnX. The SQL Server CASE statement ends with the END clause.

条件可以是任何返回布尔值的有效SQL Server表达式。 例如，条件可以是model> 2000，在条件之后使用THEN子句。 如果CONDITION返回true，则THEN子句后面的值存储在columnX中。 否则，ELSE子句之后的值也将存储在columnX中。 SQL Server CASE语句以END子句结尾。

CASE语句示例 (CASE statement examples)

Let’s now see the CASE statement in action.

现在让我们看看CASE语句的作用。

In a previous section, we created a table named Cars inside the ShowRoom database.

在上一节中，我们在ShowRoom数据库中创建了一个名为Cars的表。

The condition column had the value X for all rows. We will use the SQL Server CASE statement to set the value of the condition column to “New” if the model column has a value greater than 2000, otherwise the value for the condition column will be set to “Old”.

所有行的条件列的值均为X。 如果模型列的值大于2000，我们将使用SQL Server CASE语句将条件列的值设置为“新建”，否则条件列的值将设置为“旧”。

Look at the following script:

看下面的脚本：

SELECT name,
          model,
          CASE WHEN model > 2000 THEN 'New'
             ELSE 'Old' END AS condition
  FROM Cars

The above script displays the name, model and condition columns from the Cars table. The output of the script above looks like this:

上面的脚本显示Cars表中的name，model和condition列。 上面脚本的输出如下所示：

You can see that the value of X in the condition column has been replaced by “New” and “Old” depending upon the model of the car.

您会看到，根据汽车的型号，条件栏中的X值已被“新”和“旧”替换。

CASE语句中的多个条件 (Multiple conditions in CASE statement)

You can evaluate multiple conditions in the CASE statement.

您可以在CASE语句中评估多个条件。

Let’s write a SQL Server CASE statement which sets the value of the condition column to “New” if the value in the model column is greater than 2010, to ‘Average’ if the value in the model column is greater than 2000, and to ‘Old’ if the value in the model column is greater than 1990.

让我们编写一条SQL Server CASE语句，如果model列中的值大于2010，则将条件列的值设置为“ New”，如果model列中的值大于2000，则将条件列的值设置为“ Average”，并将其设置为“如果模型列中的值大于1990，则为“旧”。

Look at the following script:

看下面的脚本：

SELECT name,
       model,
       CASE WHEN model > 2010 THEN 'New'
      WHEN model > 2000 THEN 'Average'
      WHEN model > 1990 THEN 'Old'
                ELSE 'Old' END AS condition
  FROM Cars

The output of the script above looks like this:

上面脚本的输出如下所示：

In the script above, we assigned three different values to the condition column depending on the value in the model column.

在上面的脚本中，我们根据模型列中的值将三个不同的值分配给条件列。

However, in the above script, the conditions are overlapping as. the model with a value greater than 2010 also has a value greater than 2000 and 1990. A better way to implement multiple conditions is to use logical operators like AND, OR, NOT, etc. Look at the following script:

但是，在上面的脚本中，条件是重叠的。 值大于2010的模型的值也大于2000和1990。实现多个条件的更好方法是使用逻辑运算符，例如AND，OR，NOT等。请看以下脚本：

SELECT name,
       model,
       CASE WHEN model > 2010 THEN 'New'
      WHEN model > 2000 AND model <2010 THEN 'Average'
      WHEN model > 1990 AND model <2000 THEN 'Old'
            ELSE 'Old' END AS condition
  FROM Cars

The output of the script above looks like this:

上面脚本的输出如下所示：

We can also evaluate multiple conditions from different columns using the SQL Server CASE statement. In the following example, we will assign the value of “New White” to the condition column where the model is greater than 2010 and the color is white. Look at the following script:

我们还可以使用SQL Server CASE语句从不同的列评估多个条件。 在下面的示例中，我们将“ New White”的值分配给条件列，其中条件模型大于2010并且颜色为白色。 看下面的脚本：

SELECT name,
     color,
       model,
       CASE WHEN model > 2010 AND color = 'white' THEN 'New White'
      WHEN model > 2010 THEN 'New'
      WHEN model > 2000 AND model <2010 THEN 'Average'
      WHEN model > 1990 AND model <2000 THEN 'Old'
            ELSE 'Old' END AS condition
  FROM Cars

The output looks like this:

输出看起来像这样：

You can see from row 5 that since the color is white and the model is greater than 2010, value for the condition column has been set to “New White” in the output.

从第5行可以看到，由于颜色为白色且模型大于2010，因此条件列的值在输出中已设置为“ New White”。

在SQL Server CASE语句中使用GROUP BY (Using GROUP BY with SQL Server CASE statement)

The CASE statement can also be used in conjunction with the GROUP BY statement in order to apply aggregate functions.

CASE语句也可以与GROUP BY语句结合使用，以应用聚合函数。

For example, if we want to count the number of new (model number greater than 2000) and old (model number less than 2000) vehicles, we can use the GROUP BY clause with the CASE statement as follows:

例如，如果我们要计算新车（型号大于2000）和旧车（型号小于2000）的数量，可以将GROUP BY子句与CASE语句一起使用，如下所示：

SELECT 
       CASE WHEN model > 2000 THEN 'New'
            ELSE 'Old' END AS condition,
      COUNT(1) AS count
  FROM Cars
  GROUP BY CASE WHEN model > 2000 THEN 'New'
            ELSE 'Old' END

In the script above we use the COUNT aggregate function with the CASE statement.

在上面的脚本中，我们将COUNT聚合函数与CASE语句一起使用。

The SQL Server CASE statement sets the value of the condition column to “New” or “Old”. Inside the GROUP BY clause, we specify that the corresponding count for “New” is incremented by 1, whenever a model value of greater than 2000 is encountered. The Else section means that we increase the count for “Old” by 1 if the value of the model is 2000 or less. The output of the script above looks like this:

SQL Server CASE语句将条件列的值设置为“ New”或“ Old”。 在GROUP BY子句中，我们指定当遇到大于2000的模型值时，“ New”的相应计数增加1。 Else部分表示如果模型的值小于或等于2000，则将“ Old”的计数增加1。 上面脚本的输出如下所示：

Since our dataset has five vehicles with a value for ‘model’ of greater than 2000, you can see a 5 in the count column for “New” vehicles. Similarly, we had 5 old vehicles and hence we can see 5 for the count column of “Old” vehicles.

由于我们的数据集中有五辆车的“模型”值大于2000，因此您可以在“新”车的计数列中看到5。 同样，我们有5辆旧车，因此“旧”车的计数列中可以看到5辆。

Similarly, we can GROUP BY more than two values. Look at the following script:

同样，我们可以将GROUP BY设置为两个以上的值。 看下面的脚本：

SELECT 
       CASE WHEN model > 2010 THEN 'New'
      WHEN model > 2000 THEN 'Average'
      WHEN model > 1990 THEN 'Old'
            ELSE 'Old' END AS condition,
      COUNT(1) AS count
  FROM Cars
  GROUP BY CASE WHEN model > 2010 THEN 'New'
      WHEN model > 2000 THEN 'Average'
      WHEN model > 1990 THEN 'Old'
            ELSE 'Old' END

In the script above, we grouped the data into three categories: “New”, “Average” and “Old”.

在上面的脚本中，我们将数据分为三类：“新”，“平均”和“旧”。

The output looks like this:

输出看起来像这样：

You can see the count for “New”, “Average” and “Old” condition cars.

您可以看到“新”，“平均”和“旧”状态车的数量。

结论 (Conclusion)

The CASE statement comes in handy when you want to implement IF-THEN logic in SQL Server. In this article, we saw what CASE statement is along with its syntax. We also saw different examples of CASE statement along with its usage with the GROUP BY clause.

当您想在SQL Server中实现IF-THEN逻辑时，CASE语句会派上用场。 在本文中，我们了解了CASE语句及其语法。 我们还看到了CASE语句的不同示例以及它与GROUP BY子句的用法。

本的其他精彩文章 (Other great articles from Ben)

• Understanding SQL Server query plan cache了解SQL Server查询计划缓存
• What is the difference between Clustered and Non-Clustered Indexes in SQL Server?SQL Server中的聚集索引和非聚集索引有什么区别？
• How to use window functions如何使用视窗功能
• Querying data using SQL Server CASE statement使用SQL Server CASE语句查询数据

翻译自: https://www.sqlshack.com/understanding-sql-server-case-statement/