0
0

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

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

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

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

-- 建表CREATETABLE`products`(`productID`int(11)NOTNULL,`productName`varchar(255)DEFAULTNULL,`categoryID`int(11)DEFAULTNULL,`unitPrice`int(11)DEFAULTNULL,`stockQuantity`int(11)DEFAULTNULL,PRIMARYKEY(`productID`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 样例数据INSERTINTO`products`VALUES(1,'Laptop',1,800,50);INSERTINTO`products`VALUES(2,'Smartphone',1,500,100);INSERTINTO`products`VALUES(3,'T-shirt',2,20,200);INSERTINTO`products`VALUES(4,'Jeans',2,40,150);INSERTINTO`products`VALUES(5,'Headphones',1,100,75);
  • 示例展示
    productIDproductNamecategoryIDunitPricestockQuantity1Laptop1800502Smartphone15001003T-shirt2202004Jeans2401505Headphones110075

    一. CASE WHEN 基础使用

1. CASE WHEN-基本使用

SELECT
    ProductName,
    UnitPrice,CASEWHEN UnitPrice >100THEN'Expensive'ELSE'Affordable'ENDAS PriceCategory 
FROM
    Products;

查询结果：
ProductNameUnitPricePriceCategoryLaptop800ExpensiveSmartphone500ExpensiveT-shirt20AffordableJeans40AffordableHeadphones100Affordable

2. CASE WHEN-多条件

SELECT
    productName,
    stockQuantity,CASEWHEN stockQuantity >100THEN'In Stock'WHEN stockQuantity >50THEN'Limited Stock'ELSE'Out of Stock'ENDAS StockStatus 
FROM
    products;

查询结果：
productNamestockQuantityStockStatusLaptop50Out of StockSmartphone100Limited StockT-shirt200In StockJeans150In StockHeadphones75Limited Stock

3. CASE WHEN-聚合函数

SELECT
    categoryID,AVG( unitPrice )AS AvgPrice,CASEWHENAVG( unitPrice )>50THEN'High Price'ELSE'Low Price'ENDAS PriceCategory 
FROM
    products 
GROUPBY
    categoryID;
  • 查询结果
    categoryIDAvgPricePriceCategory1466.6667Hign Price230low Price

    4. CASE WHEN-日期条件

SELECT
    productName,CASEWHEN EXTRACT(MONTHFROMCURRENT_DATE)=8THEN(SELECTNOW())ELSE'Other Month'ENDAS CurrentTime 
FROM
    products;
  • 查询结果
    productNameCurrentTimeLaptop2023/8/30 19:14Smartphone2023/8/30 19:14T-shirt2023/8/30 19:14Jeans2023/8/30 19:14Headphones2023/8/30 19:14

    5. CASE WHEN-用于排序

SELECT
    ProductName,
    UnitPrice,CASEWHEN UnitPrice >50THEN'Expensive'ELSE'Affordable'ENDAS PriceCategory
FROM Products
ORDERBY UnitPrice DESC;
  • 查询结果
    productNameunitPricePriceCategoryLaptop1902/3/10 0:00ExpensiveSmartphone1901/5/14 0:00ExpensiveHeadphones1900/4/9 0:00ExpensiveJeans1900/2/9 0:00AffordableT-shirt1900/1/20 0:00Affordable

    6. CASE WHEN-子查询

SELECT
    productName,
    unitPrice,(CASEWHEN unitPrice >(SELECTAVG( unitPrice )FROM products )THEN'Above Avg'ELSE'Below Avg'END)AS PriceComparison 
FROM
    products;
  • 查询结果
    productNameunitPricePriceComparisonLaptop800Above AvgSmartphone500Above AvgT-shirt20Below AvgJeans40Below AvgHeadphones100Below Avg

    7. CASE WHEN-计算字段

SELECT
    ProductName,
    UnitPrice,
    StockQuantity,CASEWHEN StockQuantity >0THEN UnitPrice / StockQuantity
        ELSE0ENDAS PricePerUnit
FROM Products;
  • 查询结果
    productNameunitPricestockQuantityPricePerUnitLaptop8005016Smartphone5001005T-shirt202000.1Jeans401500.2667Headphones100751.3333

    8. CASE WHEN-动态列名

SELECT
    productName,
    unitPrice,
    stockQuantity,CASEWHEN stockQuantity >150THEN'High'WHEN stockQuantity >100THEN'Medium'ELSE'Low'ENDAS StockCategory,CASEWHEN stockQuantity >100THEN
        stockQuantity *1.1ELSE stockQuantity *1.05ENDAS AdjustedStock 
FROM
    products;
  • 查询结果
    productNameunitPricestockQuantityStockCategoryAdjustedStockLaptop80050Low52.5Smartphone500100Low105T-shirt20200High220Jeans40150Medium165Headphones10075Low78.75

    9. CASE WHEN-带有嵌套逻辑

SELECT
    ProductName,
    UnitPrice,CASEWHEN StockQuantity >100THENCASEWHEN UnitPrice >50THEN'High Demand, High Price'ELSE'High Demand, Affordable'ENDELSE'Low Demand'ENDAS ProductStatus
FROM Products;
  • 查询结果
    productNameunitPriceProductStatusLaptop800Low DemandSmartphone500Low DemandT-shirt20High Demand, AffordableJeans40High Demand, AffordableHeadphones100Low Demand

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

SELECT
    ProductName,CASEWHEN ProductName LIKE'%Laptop%'THEN'Electronics'WHEN ProductName LIKE'%T-shirt%'THEN'Clothing'ELSE'Other'ENDAS Category
FROM Products;
  • 查询结果
    productNameCategoryLaptopElectronicsSmartphoneOtherT-shirtClothingJeansOtherHeadphonesOther

    11. CASE WHEN-用于条件合并

SELECT
    ProductName,
    UnitPrice,CASEWHEN UnitPrice >50AND StockQuantity >50THEN'High Price, High Stock'WHEN UnitPrice >50OR StockQuantity >50THEN'High Price or High Stock'ELSE'Low Price and Low Stock'ENDAS ProductStatus
FROM Products;
  • 查询结果
    productNameunitPriceProductStatusLaptop800High Price or High StockSmartphone500High Price, High StockT-shirt20High Price or High StockJeans40High Price or High StockHeadphones100High Price, High Stock

    12. CASE WHEN-处理多列

SELECT
    ProductName,
    UnitPrice,
    StockQuantity,CASEWHEN StockQuantity >50AND UnitPrice <30THEN'Popular and Affordable'WHEN StockQuantity <=50AND UnitPrice <30THEN'Limited Stock, Affordable'WHEN StockQuantity >50AND UnitPrice >=30THEN'Popular and Expensive'ELSE'Limited Stock, Expensive'ENDAS ProductCategory
FROM Products;
  • 查询结果
    productNameunitPricestockQuantityProductCategoryLaptop80050Limited Stock, ExpensiveSmartphone500100Popular and ExpensiveT-shirt20200Popular and AffordableJeans40150Popular and ExpensiveHeadphones10075Popular and Expensive

    13. CASE WHEN-加入窗口函数

SELECT
    ProductName,
    UnitPrice,
    StockQuantity,CASEWHEN StockQuantity >AVG(StockQuantity)OVER()THEN'Above Avg Stock'ELSE'Below Avg Stock'ENDAS StockComparison
FROM Products;
  • 查询结果
    productNameunitPricestockQuantityStockComparisonT-shirt20200Above Avg StockLaptop80050Below Avg StockJeans40150Above Avg StockSmartphone500100Below Avg StockHeadphones10075Below Avg Stock

    二. CASE WHEN 进阶使用

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

样例SQL：

SELECT
    p.ProductID,
    p.ProductName,
    s.SaleDate,
    s.QuantitySold,CASEWHEN s.QuantitySold > LAG(s.QuantitySold)OVER(PARTITIONBY p.ProductID ORDERBY s.SaleDate)THEN'Increased'WHEN s.QuantitySold < LAG(s.QuantitySold)OVER(PARTITIONBY p.ProductID ORDERBY s.SaleDate)THEN'Decreased'ELSE'Stable'ENDAS Trend
FROM Products p
JOIN SalesHistory s ON p.ProductID = s.ProductID;

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

样例SQL：

SELECT
    o.OrderID,
    o.OrderDate,SUM(CASEWHEN p.CategoryID =1THEN o.Quantity ELSE0END)AS ElectronicsQuantity,SUM(CASEWHEN p.CategoryID =2THEN o.Quantity ELSE0END)AS ClothingQuantity,SUM(CASEWHEN p.CategoryID =3THEN o.Quantity ELSE0END)AS OtherQuantity
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUPBY o.OrderID, o.OrderDate;

3. 多层次CASE WHEN分析

样例SQL：

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

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

样例SQL：

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

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

样例SQL：

SELECT
    OrderID,
    OrderDate,SUM(CASEWHEN Quantity * Price >500THEN Quantity ELSE0END)AS HighValueItems,SUM(CASEWHEN Quantity * Price >100AND Quantity * Price <=500THEN Quantity ELSE0END)AS MediumValueItems,SUM(CASEWHEN Quantity * Price <=100THEN Quantity ELSE0END)AS LowValueItems
FROM Orders
GROUPBY OrderID, OrderDate;

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

1. 数据重编码

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

SELECT
    customerName,CASEWHEN customerType ='Individual'THEN1WHEN customerType ='Corporate'THEN2ELSE0ENDAS CustomerTypeCode
FROM Customers;

2. 条件分组

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

SELECT
    productName,SUM(quantity)AS totalQuantity,CASEWHENSUM(quantity)>100THEN'High'WHENSUM(quantity)>50THEN'Medium'ELSE'Low'ENDAS QuantityGroup
FROM Sales
GROUPBY productName;

3. 动态排序规则

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

SELECT
    productName,
    unitPrice
FROM Products
ORDERBYCASEWHEN category ='Electronics'THEN unitPrice
        WHEN category ='Clothing'THEN unitPrice *0.9ELSE unitPrice *1.1END;

4. 分位数分析

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

SELECT
    productName,
    unitPrice,CASEWHEN unitPrice <= PERCENTILE_CONT(0.25)WITHINGROUP(ORDERBY unitPrice)THEN'Q1'WHEN unitPrice <= PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBY unitPrice)THEN'Q2'WHEN unitPrice <= PERCENTILE_CONT(0.75)WITHINGROUP(ORDERBY unitPrice)THEN'Q3'ELSE'Q4'ENDAS PriceQuartile
FROM Products;

5. 缺失数据填充

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

SELECT
    orderID,
    orderDate,CASEWHEN orderAmount ISNULLTHEN0ELSE orderAmount
    ENDAS FilledOrderAmount
FROM Orders;

6. 日期区间分析

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

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

7. 业务阶段分析

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

SELECT
    userID,
    registrationDate,CASEWHENNOW()- registrationDate <INTERVAL'30 days'THEN'New User'WHENNOW()- registrationDate <INTERVAL'90 days'THEN'Regular User'ELSE'Inactive User'ENDAS UserStage
FROM Users;

8. 动态列选择

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

SELECT
    orderID,
    orderDate,CASEWHEN displayPrice ='Gross'THEN grossPrice
        ELSE netPrice
    ENDAS SelectedPrice
FROM Orders;

9. 异常值标记

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

SELECT
    customerID,
    orderDate,
    orderAmount,CASEWHEN orderAmount <0THEN'Negative'WHEN orderAmount >10000THEN'High Amount'ELSE'Normal'ENDAS DataQuality
FROM Orders;

10. 数据格式转换

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

SELECT
    productID,
    productName,
    inStock,CASEWHEN inStock THEN'Available'ELSE'Out of Stock'ENDAS StockStatus
FROM Products;
标签： sql 数据库 mysql
本文转载自: https://blog.csdn.net/wt334502157/article/details/132587836
版权归原作者 王亭_666 所有， 如有侵权，请联系我们删除。
登录后发布评论

“SQL中的CASE WHEN语句：从基础到高级应用指南”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

美国人工智能国家安全备忘录核心解读（下）

【网络】NDS协议 | ICMP协议 | NAT技术

单元测试无数据库记录：unit-record深度解析

探索数据，洞见未来——第二届大数据与数据挖掘国际会议（BDDM 2024）诚邀参会投稿！

[RabbitMQ] 重试机制+TTL+死信队列

前端部署项目到服务器（宝塔面板·腾讯云专享版），vue项目为例

猫头虎 分享：免费使用Claude 3.5 的AI代码编辑器 Windsurf Editor，全面解析！

文章导航