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: </