SQL中的CASE WHEN语句：从基础到高级应用指南

SQL中的CASE WHEN语句：从基础到高级应用指南

SQL中的CASE WHEN语句：从基础到高级应用指南

准备工作 - 表1: products 示例数据：

我们使用一个名为"Products"的表，包含以下列：ProductID、ProductName、CategoryID、UnitPrice、StockQuantity。

-- 建表
CREATE TABLE `products` (`productID` int(11) NOT NULL,`productName` varchar(255) DEFAULT NULL,`categoryID` int(11) DEFAULT NULL,`unitPrice` int(11) DEFAULT NULL,`stockQuantity` int(11) DEFAULT NULL,PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 样例数据
INSERT INTO `products` VALUES (1, 'Laptop', 1, 800, 50);
INSERT INTO `products` VALUES (2, 'Smartphone', 1, 500, 100);
INSERT INTO `products` VALUES (3, 'T-shirt', 2, 20, 200);
INSERT INTO `products` VALUES (4, 'Jeans', 2, 40, 150);
INSERT INTO `products` VALUES (5, 'Headphones', 1, 100, 75);

• 示例展示

productID	productName	categoryID	unitPrice	stockQuantity
1	Laptop	1	800	50
2	Smartphone	1	500	100
3	T-shirt	2	20	200
4	Jeans	2	40	150
5	Headphones	1	100	75

一. CASE WHEN 基础使用

1. CASE WHEN-基本使用

SELECTProductName,UnitPrice,
CASEWHEN UnitPrice > 100 THEN'Expensive' ELSE 'Affordable' END AS PriceCategory 
FROMProducts;

查询结果：

ProductName	UnitPrice	PriceCategory
Laptop	800	Expensive
Smartphone	500	Expensive
T-shirt	20	Affordable
Jeans	40	Affordable
Headphones	100	Affordable

2. CASE WHEN-多条件

SELECTproductName,stockQuantity,
CASEWHEN stockQuantity > 100 THEN'In Stock' WHEN stockQuantity > 50 THEN'Limited Stock' ELSE 'Out of Stock' END AS StockStatus 
FROMproducts;

查询结果：

productName	stockQuantity	StockStatus
Laptop	50	Out of Stock
Smartphone	100	Limited Stock
T-shirt	200	In Stock
Jeans	150	In Stock
Headphones	75	Limited Stock

3. CASE WHEN-聚合函数

SELECTcategoryID,AVG( unitPrice ) AS AvgPrice,
CASEWHEN AVG( unitPrice ) > 50 THEN'High Price' ELSE 'Low Price' END AS PriceCategory 
FROMproducts 
GROUP BYcategoryID;

• 查询结果

categoryID	AvgPrice	PriceCategory
1	466.6667	Hign Price
2	30	low Price

4. CASE WHEN-日期条件

SELECTproductName,
CASEWHEN EXTRACT( MONTH FROM CURRENT_DATE ) = 8 THEN( SELECT NOW() ) ELSE 'Other Month' END AS CurrentTime 
FROMproducts;

• 查询结果

productName	CurrentTime
Laptop	2023/8/30 19:14
Smartphone	2023/8/30 19:14
T-shirt	2023/8/30 19:14
Jeans	2023/8/30 19:14
Headphones	2023/8/30 19:14

5. CASE WHEN-用于排序

SELECTProductName,UnitPrice,CASEWHEN UnitPrice > 50 THEN 'Expensive'ELSE 'Affordable'END AS PriceCategory
FROM Products
ORDER BY UnitPrice DESC;

• 查询结果

productName	unitPrice	PriceCategory
Laptop	1902/3/10 0:00	Expensive
Smartphone	1901/5/14 0:00	Expensive
Headphones	1900/4/9 0:00	Expensive
Jeans	1900/2/9 0:00	Affordable
T-shirt	1900/1/20 0:00	Affordable

6. CASE WHEN-子查询

SELECTproductName,unitPrice,( CASE WHEN unitPrice > ( SELECT AVG( unitPrice ) FROM products ) THEN 'Above Avg' ELSE 'Below Avg' END ) AS PriceComparison 
FROMproducts;

• 查询结果

productName	unitPrice	PriceComparison
Laptop	800	Above Avg
Smartphone	500	Above Avg
T-shirt	20	Below Avg
Jeans	40	Below Avg
Headphones	100	Below Avg

7. CASE WHEN-计算字段

SELECTProductName,UnitPrice,StockQuantity,CASEWHEN StockQuantity > 0 THEN UnitPrice / StockQuantityELSE 0END AS PricePerUnit
FROM Products;

• 查询结果

productName	unitPrice	stockQuantity	PricePerUnit
Laptop	800	50	16
Smartphone	500	100	5
T-shirt	20	200	0.1
Jeans	40	150	0.2667
Headphones	100	75	1.3333

8. CASE WHEN-动态列名

SELECTproductName,unitPrice,stockQuantity,
CASEWHEN stockQuantity > 150 THEN'High' WHEN stockQuantity > 100 THEN'Medium' ELSE 'Low' END AS StockCategory,
CASEWHEN stockQuantity > 100 THENstockQuantity * 1.1 ELSE stockQuantity * 1.05 END AS AdjustedStock 
FROMproducts;

• 查询结果

productName	unitPrice	stockQuantity	StockCategory	AdjustedStock
Laptop	800	50	Low	52.5
Smartphone	500	100	Low	105
T-shirt	20	200	High	220
Jeans	40	150	Medium	165
Headphones	100	75	Low	78.75

9. CASE WHEN-带有嵌套逻辑

SELECTProductName,UnitPrice,CASEWHEN StockQuantity > 100 THENCASEWHEN UnitPrice > 50 THEN 'High Demand, High Price'ELSE 'High Demand, Affordable'ENDELSE 'Low Demand'END AS ProductStatus
FROM Products;

• 查询结果

productName	unitPrice	ProductStatus
Laptop	800	Low Demand
Smartphone	500	Low Demand
T-shirt	20	High Demand, Affordable
Jeans	40	High Demand, Affordable
Headphones	100	Low Demand

10. CASE WHEN-处理字符串匹配

SELECTProductName,CASEWHEN ProductName LIKE '%Laptop%' THEN 'Electronics'WHEN ProductName LIKE '%T-shirt%' THEN 'Clothing'ELSE 'Other'END AS Category
FROM Products;

• 查询结果

productName	Category
Laptop	Electronics
Smartphone	Other
T-shirt	Clothing
Jeans	Other
Headphones	Other

11. CASE WHEN-用于条件合并

SELECTProductName,UnitPrice,CASEWHEN UnitPrice > 50 AND StockQuantity > 50 THEN 'High Price, High Stock'WHEN UnitPrice > 50 OR StockQuantity > 50 THEN 'High Price or High Stock'ELSE 'Low Price and Low Stock'END AS ProductStatus
FROM Products;

• 查询结果

productName	unitPrice	ProductStatus
Laptop	800	High Price or High Stock
Smartphone	500	High Price, High Stock
T-shirt	20	High Price or High Stock
Jeans	40	High Price or High Stock
Headphones	100	High Price, High Stock

12. CASE WHEN-处理多列

SELECTProductName,UnitPrice,StockQuantity,CASEWHEN StockQuantity > 50 AND UnitPrice <30 THEN 'Popular and Affordable'WHEN StockQuantity <= 50 AND UnitPrice < 30 THEN 'Limited Stock, Affordable'WHEN StockQuantity > 50 AND UnitPrice >= 30 THEN 'Popular and Expensive'ELSE 'Limited Stock, Expensive'END AS ProductCategory
FROM Products;

• 查询结果

productName	unitPrice	stockQuantity	ProductCategory
Laptop	800	50	Limited Stock, Expensive
Smartphone	500	100	Popular and Expensive
T-shirt	20	200	Popular and Affordable
Jeans	40	150	Popular and Expensive
Headphones	100	75	Popular and Expensive

13. CASE WHEN-加入窗口函数

SELECTProductName,UnitPrice,StockQuantity,CASEWHEN StockQuantity > AVG(StockQuantity) OVER () THEN 'Above Avg Stock'ELSE 'Below Avg Stock'END AS StockComparison
FROM Products;

• 查询结果

productName	unitPrice	stockQuantity	StockComparison
T-shirt	20	200	Above Avg Stock
Laptop	800	50	Below Avg Stock
Jeans	40	150	Above Avg Stock
Smartphone	500	100	Below Avg Stock
Headphones	100	75	Below Avg Stock

二. CASE WHEN 进阶使用

1. 基于历史数据的趋势预测

样例SQL：

SELECTp.ProductID,p.ProductName,s.SaleDate,s.QuantitySold,CASEWHEN s.QuantitySold > LAG(s.QuantitySold) OVER (PARTITION BY p.ProductID ORDER BY s.SaleDate) THEN 'Increased'WHEN s.QuantitySold < LAG(s.QuantitySold) OVER (PARTITION BY p.ProductID ORDER BY s.SaleDate) THEN 'Decreased'ELSE 'Stable'END AS Trend
FROM Products p
JOIN SalesHistory s ON p.ProductID = s.ProductID;

2. 基于不同维度的复杂分析

样例SQL：

SELECTo.OrderID,o.OrderDate,SUM(CASE WHEN p.CategoryID = 1 THEN o.Quantity ELSE 0 END) AS ElectronicsQuantity,SUM(CASE WHEN p.CategoryID = 2 THEN o.Quantity ELSE 0 END) AS ClothingQuantity,SUM(CASE WHEN p.CategoryID = 3 THEN o.Quantity ELSE 0 END) AS OtherQuantity
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY o.OrderID, o.OrderDate;

3. 多层次CASE WHEN分析

样例SQL：

SELECTEmployeeID,FirstName,LastName,Salary,CASEWHEN Salary > 70000 THEN 'High'WHEN Salary > 60000 THEN 'Medium'WHEN Salary > 50000 THEN 'Low'ELSE 'Very Low'END AS SalaryLevel,CASEWHEN Salary > 60000 THEN 'Above Average'ELSE 'Below Average'END AS SalaryComparison
FROM Employees;

4. 使用CASE WHEN进行数据分桶

样例SQL：

SELECTCustomerID,Age,Gender,CASEWHEN Age < 30 THEN 'Young'WHEN Age >= 30 AND Age < 40 THEN 'Middle-aged'ELSE 'Senior'END AS AgeGroup,CASEWHEN Gender = 'Male' THEN 'Male'WHEN Gender = 'Female' THEN 'Female'ELSE 'Other'END AS GenderCategory
FROM Customers;

5. 基于多条件的复杂逻辑判断

样例SQL：

SELECTOrderID,OrderDate,SUM(CASE WHEN Quantity * Price > 500 THEN Quantity ELSE 0 END) AS HighValueItems,SUM(CASE WHEN Quantity * Price > 100 AND Quantity * Price <= 500 THEN Quantity ELSE 0 END) AS MediumValueItems,SUM(CASE WHEN Quantity * Price <= 100 THEN Quantity ELSE 0 END) AS LowValueItems
FROM Orders
GROUP BY OrderID, OrderDate;

三. CASE WHEN 业务场景常用技巧

1. 数据重编码

您可以使用CASE WHEN来对现有数据进行重新编码，例如将文本值转换为数字编码或将某些字符串转换为更易于处理的标识符。

SELECTcustomerName,CASEWHEN customerType = 'Individual' THEN 1WHEN customerType = 'Corporate' THEN 2ELSE 0END AS CustomerTypeCode
FROM Customers;

2. 条件分组

使用CASE WHEN可以在查询结果中创建不同的数据分组，而无需在实际数据中创建新的列。

SELECTproductName,SUM(quantity) AS totalQuantity,CASEWHEN SUM(quantity) > 100 THEN 'High'WHEN SUM(quantity) > 50 THEN 'Medium'ELSE 'Low'END AS QuantityGroup
FROM Sales
GROUP BY productName;

3. 动态排序规则

通过在ORDER BY子句中使用CASE WHEN，您可以根据不同条件动态调整查询结果的排序规则。

SELECTproductName,unitPrice
FROM Products
ORDER BYCASEWHEN category = 'Electronics' THEN unitPriceWHEN category = 'Clothing' THEN unitPrice * 0.9ELSE unitPrice * 1.1END;

4. 分位数分析

使用CASE WHEN可以在查询结果中对数据进行分位数分析，识别哪些数据点位于不同的分位数区间。

SELECTproductName,unitPrice,CASEWHEN unitPrice <= PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY unitPrice) THEN 'Q1'WHEN unitPrice <= PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY unitPrice) THEN 'Q2'WHEN unitPrice <= PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY unitPrice) THEN 'Q3'ELSE 'Q4'END AS PriceQuartile
FROM Products;

5. 缺失数据填充

使用CASE WHEN可以根据条件将缺失的数据点填充为特定值，从而更好地处理数据缺失情况。

SELECTorderID,orderDate,CASEWHEN orderAmount IS NULL THEN 0ELSE orderAmountEND AS FilledOrderAmount
FROM Orders;

6. 日期区间分析

使用CASE WHEN可以对日期进行区间分析，例如判断每个日期属于哪个季节、哪个月份等。

SELECTorderDate,CASEWHEN EXTRACT(MONTH FROM orderDate) IN (12, 1, 2) THEN 'Winter'WHEN EXTRACT(MONTH FROM orderDate) IN (3, 4, 5) THEN 'Spring'WHEN EXTRACT(MONTH FROM orderDate) IN (6, 7, 8) THEN 'Summer'ELSE 'Fall'END AS Season
FROM Orders;

7. 业务阶段分析

使用CASE WHEN可以根据特定业务规则判断数据所处的不同阶段，如用户生命周期阶段、订单处理阶段等。

SELECTuserID,registrationDate,CASEWHEN NOW() - registrationDate < INTERVAL '30 days' THEN 'New User'WHEN NOW() - registrationDate < INTERVAL '90 days' THEN 'Regular User'ELSE 'Inactive User'END AS UserStage
FROM Users;

8. 动态列选择

使用CASE WHEN可以在查询结果中根据条件选择不同的列，从而根据业务需求定制查询结果。

SELECTorderID,orderDate,CASEWHEN displayPrice = 'Gross' THEN grossPriceELSE netPriceEND AS SelectedPrice
FROM Orders;

9. 异常值标记

使用CASE WHEN可以根据条件识别和标记异常数据点，帮助进行数据质量分析。

SELECTcustomerID,orderDate,orderAmount,CASEWHEN orderAmount < 0 THEN 'Negative'WHEN orderAmount > 10000 THEN 'High Amount'ELSE 'Normal'END AS DataQuality
FROM Orders;

10. 数据格式转换

使用CASE WHEN可以在不同的数据格式之间进行转换，例如将布尔值转换为文本标签。

SELECTproductID,productName,inStock,CASEWHEN inStock THEN 'Available'ELSE 'Out of Stock'END AS StockStatus
FROM Products;