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);
- 示例展示
一. CASE WHEN 基础使用
1. CASE WHEN-基本使用
SELECT
ProductName,
UnitPrice,
CASE
WHEN UnitPrice > 100 THEN
'Expensive' ELSE 'Affordable'
END AS PriceCategory
FROM
Products;
查询结果:
2. CASE WHEN-多条件
SELECT
productName,
stockQuantity,
CASE
WHEN stockQuantity > 100 THEN
'In Stock'
WHEN stockQuantity > 50 THEN
'Limited Stock' ELSE 'Out of Stock'
END AS StockStatus
FROM
products;
查询结果:
3. CASE WHEN-聚合函数
SELECT
categoryID,
AVG( unitPrice ) AS AvgPrice,
CASE
WHEN AVG( unitPrice ) > 50 THEN
'High Price' ELSE 'Low Price'
END AS PriceCategory
FROM
products
GROUP BY
categoryID;
- 查询结果
4. CASE WHEN-日期条件
SELECT
productName,
CASE
WHEN EXTRACT( MONTH FROM CURRENT_DATE ) = 8 THEN
( SELECT NOW() ) ELSE 'Other Month'
END AS CurrentTime
FROM
products;
- 查询结果
5. CASE WHEN-用于排序
SELECT
ProductName,
UnitPrice,
CASE
WHEN UnitPrice > 50 THEN 'Expensive'
ELSE 'Affordable'
END AS PriceCategory
FROM Products
ORDER BY UnitPrice DESC;
- 查询结果
6. CASE WHEN-子查询
SELECT
productName,
unitPrice,
( CASE WHEN unitPrice > ( SELECT AVG( unitPrice ) FROM products ) THEN 'Above Avg' ELSE 'Below Avg' END ) AS PriceComparison
FROM
products;
- 查询结果
7. CASE WHEN-计算字段
SELECT
ProductName,
UnitPrice,
StockQuantity,
CASE
WHEN StockQuantity > 0 THEN UnitPrice / StockQuantity
ELSE 0
END AS PricePerUnit
FROM Products;
- 查询结果
8. CASE WHEN-动态列名
SELECT
productName,
unitPrice,
stockQuantity,
CASE
WHEN stockQuantity > 150 THEN
'High'
WHEN stockQuantity > 100 THEN
'Medium' ELSE 'Low'
END AS StockCategory,
CASE
WHEN stockQuantity > 100 THEN
stockQuantity * 1.1 ELSE stockQuantity * 1.05
END AS AdjustedStock
FROM
products;
- 查询结果
9. CASE WHEN-带有嵌套逻辑
SELECT
ProductName,
UnitPrice,
CASE
WHEN StockQuantity > 100 THEN
CASE
WHEN UnitPrice > 50 THEN 'High Demand, High Price'
ELSE 'High Demand, Affordable'
END
ELSE 'Low Demand'
END AS ProductStatus
FROM Products;
- 查询结果
10. CASE WHEN-处理字符串匹配
SELECT
ProductName,
CASE
WHEN ProductName LIKE '%Laptop%' THEN 'Electronics'
WHEN ProductName LIKE '%T-shirt%' THEN 'Clothing'
ELSE 'Other'
END AS Category
FROM Products;
- 查询结果
11. CASE WHEN-用于条件合并
SELECT
ProductName,
UnitPrice,
CASE
WHEN 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;
- 查询结果
12. CASE WHEN-处理多列
SELECT
ProductName,
UnitPrice,
StockQuantity,
CASE
WHEN 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;
- 查询结果
13. CASE WHEN-加入窗口函数
SELECT
ProductName,
UnitPrice,
StockQuantity,
CASE
WHEN StockQuantity > AVG(StockQuantity) OVER () THEN 'Above Avg Stock'
ELSE 'Below Avg Stock'
END AS StockComparison
FROM Products;
- 查询结果
二. CASE WHEN 进阶使用
1. 基于历史数据的趋势预测
样例SQL:
SELECT
p.ProductID,
p.ProductName,
s.SaleDate,
s.QuantitySold,
CASE
WHEN 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:
SELECT
o.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:
SELECT
EmployeeID,
FirstName,
LastName,
Salary,
CASE
WHEN Salary > 70000 THEN 'High'
WHEN Salary > 60000 THEN 'Medium'
WHEN Salary > 50000 THEN 'Low'
ELSE 'Very Low'
END AS SalaryLevel,
CASE
WHEN Salary > 60000 THEN 'Above Average'
ELSE 'Below Average'
END AS SalaryComparison
FROM Employees;
4. 使用CASE WHEN进行数据分桶
样例SQL:
SELECT
CustomerID,
Age,
Gender,
CASE
WHEN Age < 30 THEN 'Young'
WHEN Age >= 30 AND Age < 40 THEN 'Middle-aged'
ELSE 'Senior'
END AS AgeGroup,
CASE
WHEN Gender = 'Male' THEN 'Male'
WHEN Gender = 'Female' THEN 'Female'
ELSE 'Other'
END AS GenderCategory
FROM Customers;
5. 基于多条件的复杂逻辑判断
样例SQL:
SELECT
OrderID,
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. 数据重编码
SELECT
customerName,
CASE
WHEN customerType = 'Individual' THEN 1
WHEN customerType = 'Corporate' THEN 2
ELSE 0
END AS CustomerTypeCode
FROM Customers;
2. 条件分组
SELECT
productName,
SUM(quantity) AS totalQuantity,
CASE
WHEN SUM(quantity) > 100 THEN 'High'
WHEN SUM(quantity) > 50 THEN 'Medium'
ELSE 'Low'
END AS QuantityGroup
FROM Sales
GROUP BY productName;
3. 动态排序规则
SELECT
productName,
unitPrice
FROM Products
ORDER BY
CASE
WHEN category = 'Electronics' THEN unitPrice
WHEN category = 'Clothing' THEN unitPrice * 0.9
ELSE unitPrice * 1.1
END;
4. 分位数分析
SELECT
productName,
unitPrice,
CASE
WHEN 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. 缺失数据填充
SELECT
orderID,
orderDate,
CASE
WHEN orderAmount IS NULL THEN 0
ELSE orderAmount
END AS FilledOrderAmount
FROM Orders;
6. 日期区间分析
SELECT
orderDate,
CASE
WHEN 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. 业务阶段分析
SELECT
userID,
registrationDate,
CASE
WHEN 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. 动态列选择
SELECT
orderID,
orderDate,
CASE
WHEN displayPrice = 'Gross' THEN grossPrice
ELSE netPrice
END AS SelectedPrice
FROM Orders;
9. 异常值标记
SELECT
customerID,
orderDate,
orderAmount,
CASE
WHEN orderAmount < 0 THEN 'Negative'
WHEN orderAmount > 10000 THEN 'High Amount'
ELSE 'Normal'
END AS DataQuality
FROM Orders;
10. 数据格式转换
SELECT
productID,
productName,
inStock,
CASE
WHEN inStock THEN 'Available'
ELSE 'Out of Stock'
END AS StockStatus
FROM Products;