北美数据科学SQL面经（CASE WHEN）

What is the CASE WHEN statement in SQL?

The CASE WHEN statement is a conditional statement in SQL that allows you to perform conditional logic and return different values based on specified conditions.

How does the CASE WHEN statement work?

The CASE WHEN statement evaluates multiple conditions and returns a result based on the first condition that evaluates to true. It allows you to perform branching logic within a query.

What is the syntax of the CASE WHEN statement?

xxxxxxxxxx
7
1
CASE
2
  WHEN condition1 THEN result1
3
  WHEN condition2 THEN result2
4
  ...
5
  ELSE resultN
6
END
7

Can you provide an example of using the CASE WHEN statement?

xxxxxxxxxx
7
1
SELECT EmployeeName,
2
       CASE
3
         WHEN Salary >= 5000 THEN 'High'
4
         WHEN Salary >= 3000 THEN 'Medium'
5
         ELSE 'Low'
6
       END AS SalaryCategory
7
FROM Employees;

What is the difference between the simple CASE and the searched CASE in the CASE WHEN statement?

In the simple CASE form, you compare a single expression with multiple possible values. In the searched CASE form, you evaluate multiple conditions independently.

Can you use the CASE WHEN statement with aggregate functions?

Yes, you can use the CASE WHEN statement with aggregate functions. It allows you to conditionally calculate aggregated values based on specified conditions.

How do you handle NULL values in the CASE WHEN statement?

You can handle NULL values in the CASE WHEN statement by including a specific condition to check for NULL, or by using the ELSE clause to handle NULL values as a default case.

Can you have multiple conditions in the CASE WHEN statement?

Yes, you can have multiple conditions in the CASE WHEN statement by using multiple WHEN clauses to specify different conditions and their corresponding results.

Can you nest CASE WHEN statements within each other?

Yes, you can nest CASE WHEN statements within each other to create more complex conditional logic. The result of an inner CASE WHEN statement can be used as a condition in an outer CASE WHEN statement.

Are there any limitations or considerations when using the CASE WHEN statement?

Some considerations when using the CASE WHEN statement include understanding the evaluation order, as only the first condition that evaluates to true is considered, and being mindful of code readability and maintainability when using nested or complex CASE WHEN statements.

How to solve a pivot table problem using the CASE WHEN statement in SQL?

假设我们有以下Sales表的数据：

我们希望创建一个透视表，将Region作为行，Product作为列，并显示每个Region中每个Product的销售总额。

使用CASE WHEN语句来实现行转列的过程如下：

xxxxxxxxxx
7
1
SELECT
2
  Region,
3
  SUM(CASE WHEN Product = 'ProductA' THEN SalesAmount ELSE 0 END) AS ProductA,
4
  SUM(CASE WHEN Product = 'ProductB' THEN SalesAmount ELSE 0 END) AS ProductB,
5
  SUM(CASE WHEN Product = 'ProductC' THEN SalesAmount ELSE 0 END) AS ProductC
6
FROM Sales
7
GROUP BY Region;

执行以上查询后，将得到以下结果：

在透视表中，每个Region作为一行，每个Product作为一列，显示了每个Region中每个Product的销售总额。

如果每个Product在每个Region只有一个唯一的SalesAmount值，那么在这种情况下，无论你使用SUM、AVG还是MIN函数，得到的结果将是一样的。因为每个CASE WHEN语句中的SalesAmount值要么是唯一的非零值，要么是零。