sql语句中case_SQL中的CASE语句

sql语句中case

The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By and Group By clause. It can be used in Insert statement as well. In this article, we would explore CASE statement and its various use cases.

SQL中的case语句返回指定条件下的值。 我们可以在选择查询中使用Case语句以及Where，Order By和Group By子句。 也可以在Insert语句中使用它。 在本文中，我们将探讨CASE语句及其各种用例。

Suppose you have a table that stores the ProductID for all products in a mini-store. You want to get Productname for a particular ProductID.

假设您有一个表，该表在迷你商店中存储所有产品的ProductID。 您想要获取特定ProductID的Productname。

Look at the following example; We declared a variable @ProductID and specified value 1 for it. In Case statement, we defined conditions. Once a condition is satisfied, its corresponding value is returned.

看下面的例子； 我们声明了一个变量@ProductID并为其指定了值1。 在案例陈述中，我们定义了条件。 一旦满足条件，将返回其对应的值。

Similarly, if we change the condition in a Case statement in SQL, it returns appropriate expression. In the following example, we want to get Product name for ProductID 4.it does not satisfy Case statement condition; therefore, it gave output from Else expression.

同样，如果我们在SQL中的Case语句中更改条件，则它将返回适当的表达式。 在下面的示例中，我们想要获取ProductID 4的产品名称。它不满足Case语句条件； 因此，它给出了Else表达式的输出。

Let us explore a few examples of the Case statement in SQL. Before we proceed, create a sample table and insert few records in it.

让我们探讨一下SQL中Case语句的一些示例。 在继续之前，创建一个示例表并在其中插入一些记录。

USE [SQLShackDemo]
GO
CREATE TABLE dbo.Employee 
( 
EmployeeID INT IDENTITY PRIMARY KEY, 
EmployeeName VARCHAR(100) NOT NULL, 
Gender VARCHAR(1) NOT NULL, 
StateCode VARCHAR(20) NOT NULL, 
Salary money NOT NULL,
) 
GO
USE [SQLShackDemo]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (201, N'Jerome', N'M', N'FL', 83000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (202, N'Ray', N'M', N'AL', 88000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (203, N'Stella', N'F', N'AL', 76000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (204, N'Gilbert', N'M', N'Ar', 42000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (205, N'Edward', N'M', N'FL', 93000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (206, N'Ernest', N'F', N'Al', 64000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (207, N'Jorge', N'F', N'IN', 75000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (208, N'Nicholas', N'F', N'Ge', 71000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (209, N'Lawrence', N'M', N'IN', 95000.0000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (210, N'Salvador', N'M', N'Co', 75000.0000)
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

We have following records in Employee table.

我们在Employee表中有以下记录。

不同格式的CASE语句 (Different Formats of CASE Statements )

一个简单的CASE语句表达式 (A simple CASE statement expression)

In this format, we evaluate one expression against multiple values. In a simple case statement, it evaluates conditions one by one. Once the condition and expression are matched, it returns the expression mentioned in THEN clause.

以这种格式，我们针对多个值评估一个表达式。 在一个简单的案例陈述中，它会逐一评估条件。 一旦条件和表达式匹配，它将返回THEN子句中提到的表达式。

We have following syntax for a case statement in SQL with a simple expression

对于带有简单表达式SQL中的case语句，我们具有以下语法

SELECT CASE Expression
When expression1 Then Result1
When expression2 Then Result2
...
ELSE Result
END

Usually, we store abbreviations in a table instead of its full form. For example, in my Employee table, I have used abbreviations in Gender and StateCode. I want to use a Case statement to return values as Male and Female in the output instead of M and F.

通常，我们将缩写存储在表格中而不是完整形式中。 例如，在我的Employee表中，我在Gender和StateCode中使用了缩写。 我想使用Case语句在输出中返回值Male和Female而不是M和F。

Execute the following code and notice that we want to evaluate CASE Gender in this query.

执行以下代码，并注意我们要在此查询中评估CASE Gender 。

In the following image, you can notice a difference in output using a Case statement in SQL.

在下图中，您可以注意到在SQL中使用Case语句的输出有所不同。

CASE语句和比较运算符 (The CASE statement and comparison operator)

In this format of a CASE statement in SQL, we can evaluate a condition using comparison operators. Once this condition is satisfied, we get an expression from corresponding THEN in the output.

以这种SQL中CASE语句的格式，我们可以使用比较运算符评估条件。 一旦满足此条件，我们将从输出中的相应THEN中获得一个表达式。

We can see the following syntax for Case statement with a comparison operator.

我们可以看到带有比较运算符的Case语句的以下语法。

CASE
    WHEN ComparsionCondition THEN result
    WHEN ComparsionCondition THEN result
    ELSE other
 END

Suppose we have a salary band for each designation. If employee salary is in between a particular range, we want to get designation using a Case statement.

假设每个职位都有一个薪水范围。 如果员工薪水在特定范围内，我们希望使用Case语句来指定。

In the following query, we are using a comparison operator and evaluate an expression.

在以下查询中，我们使用比较运算符并评估表达式。

Select EmployeeName,
 CASE
WHEN Salary >=80000 AND Salary <=100000 THEN 'Director'
WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant'
Else 'Director'
END AS Designation
from Employee

In the following image you can see, we get designation as per condition specified in CASE statement.

在下图中，您可以根据CASE语句中指定的条件获得名称。

带子句的案例陈述 (Case Statement with Order by clause)

We can use Case statement with order by clause as well. In SQL, we use Order By clause to sort results in ascending or descending order.

我们也可以将Case语句与order by子句一起使用。 在SQL中，我们使用Order By子句对结果进行升序或降序排序。

Suppose in a further example; we want to sort result in the following method.

再假设一个例子。 我们想用以下方法对结果进行排序。

1. For Female employee, employee salaries should come in descending order
   对于女员工，员工工资应按降序排列
2. For Male employee, we should get employee salaries in ascending order
   对于男性员工，我们应该按升序获得员工薪水

We can define this condition with a combination of Order by and Case statement. In the following query, you can see we specified Order By and Case together. We defined sort conditions in case expression.

我们可以使用Order by和Case语句的组合来定义此条件。 在以下查询中，您可以看到我们同时指定了Order By和Case。 我们在case表达式中定义了排序条件。

Select EmployeeName,Gender,Salary
 from Employee
 ORDER BY  CASE Gender
WHEN 'F' THEN Salary End DESC,
Case WHEN Gender='M' THEN Salary  
END

In the output, we have satisfied our sort requirement in ascending or descending order

在输出中，我们满足了升序或降序的排序要求

• For Female employee, salary is appearing in descending order
  对于女雇员，薪水以降序显示

• For Male employee, salary is appearing in ascending order
  对于男性员工，薪水以升序显示

带Group by子句SQL中的Case语句 (Case Statement in SQL with Group by clause)

We can use a Case statement with Group By clause as well. Suppose we want to group employees based on their salary. We further want to calculate the minimum and maximum salary for a particular range of employees.

我们也可以将Case语句与Group By子句一起使用。 假设我们要根据员工的薪水进行分组。 我们还想计算特定范围员工的最低和最高薪水。

In the following query, you can see that we have Group By clause and it contains i with the condition to get the required output.

在下面的查询中，您可以看到我们有Group By子句，它包含带有条件的i以获得所需的输出。

Select 
 CASE
WHEN Salary >=80000 AND Salary <=100000 THEN 'Director'
WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant'
Else 'Director'
END AS Designation,
Min(salary) as MinimumSalary,
Max(Salary) as MaximumSalary
from Employee
Group By
CASE
WHEN Salary >=80000 AND Salary <=100000 THEN 'Director'
WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant'
Else 'Director'
END

We have following output of this query. In this output, we get minimum and maximum salary for a particular designation.

我们有此查询的以下输出。 在此输出中，我们获得特定指定的最低和最高薪水。

用CASE语句更新语句 (Update statement with a CASE statement)

We can use a Case statement in SQL with update DML as well. Suppose we want to update Statecode of employees based on Case statement conditions.

我们也可以在带有更新DMLSQL中使用Case语句。 假设我们要根据Case语句条件更新员工的Statecode 。

In the following code, we are updating statecode with the following condition.

在以下代码中，我们将使用以下条件更新状态码。

• AR, then update to AR，则更新为FL FL
• GE, then update toGE，则更新为 AL AL
• For all other statecodes update value to IN
  对于所有其他状态码，将值更新为IN

Execute the following update command to fulfil our requirement using a Case statement.

使用Case语句执行以下update命令来满足我们的要求。

UPDATE employee 
SET StateCode  = CASE StateCode
 WHEN 'Ar' THEN 'FL' 
 WHEN 'GE' THEN 'AL' 
  ELSE  'IN' 
 END

In the following output, you can see old Statcode (left-hand side) and updated Statecode for the employees based on our conditions in the Case statement.

在以下输出中，您可以在Case语句中根据我们的条件查看雇员的旧Statcode（左侧）和更新的Statecode。

带CASE语句的插入语句 (Insert statement with CASE statement)

We can insert data into SQL tables as well with the help of Case statement in SQL. Suppose we have an application that inserts data into Employees table. We get the following values for gender.

我们也可以借助SQL中的Case语句将数据插入SQL表中。 假设我们有一个将数据插入到Employees表中的应用程序。 我们得到以下性别值。

Value	Description	Required value in Employee table
0	Male Employee	M
1	Female Employee	F

值	描述	员工表中的必需值
0	男员工	中号
1个	女员工	F

We do not want to insert value 0 and 1 for Male and Female employees. We need to insert the required values M and F for employee gender.

我们不想为男性和女性员工插入值0和1。 我们需要为员工性别插入所需的值M和F。

In the following query, we specified variables to store column values. In the insert statement, you can we are using a Case statement to define corresponding value to insert in the employee table. In the Case statement, it checks for the required values and inserts values from THEN expression in the table.

在以下查询中，我们指定了用于存储列值的变量。 在insert语句中，您可以使用Case语句定义要插入到employee表中的相应值。 在Case语句中，它将检查所需的值，并将THEN表达式中的值插入表中。

Declare @EmployeeName varchar(100)
Declare @Gender int
Declare @Statecode char(2)
Declare @salary money
Set @EmployeeName='Raj'
Set @Gender=0
Set @Statecode='FL'
set @salary=52000
 
Insert into employee
values 
(@EmployeeName,
CASE @Gender
WHEN 0 THEN 'M'
WHEN 1 THEN 'F'
end,
@Statecode,
@salary)

In the following screenshot, we can see newly inserted row contains Gender M instead of value 0.

在以下屏幕截图中，我们可以看到新插入的行包含Gender M而不是值0。

案例陈述限制 (Case Statement limitations)

• We cannot control the execution flow of stored procedures, functions using a Case statement in SQL
  我们无法使用SQL中的Case语句来控制存储过程，函数的执行流程
• We can have multiple conditions in Case statement; however, it works in a sequential model. If one condition is satisfied, it stops checking further conditions
  Case语句中可以有多个条件； 但是，它可以在顺序模型中工作。 如果满足一个条件，它将停止检查其他条件
• We cannot use a Case statement for checking NULL values in a table
  我们不能使用Case语句检查表中的NULL值

结论 (Conclusion)

The Case statement in SQL provides flexibility in writing t-SQL for DDL and DML queries. It also adds versatility to SQL Server queries. You should practice Case statement in your queries.

SQL中的Case语句为编写DDL和DML查询的t-SQL提供了灵活性。 它还为SQL Server查询增加了多功能性。 您应该在查询中练习案例陈述。

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

sql语句中case