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-用于排序

      SELECT    ProductName,    UnitPrice,    CASE        WHEN UnitPrice > 50 THEN 'Expensive'        ELSE 'Affordable'    END AS PriceCategoryFROM ProductsORDER 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-计算字段

          SELECT    ProductName,    UnitPrice,    StockQuantity,    CASE        WHEN StockQuantity > 0 THEN UnitPrice / StockQuantity        ELSE 0    END AS PricePerUnitFROM 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-带有嵌套逻辑

              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 ProductStatusFROM 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-处理字符串匹配

                SELECT    ProductName,    CASE        WHEN ProductName LIKE '%Laptop%' THEN 'Electronics'        WHEN ProductName LIKE '%T-shirt%' THEN 'Clothing'        ELSE 'Other'    END AS CategoryFROM Products;

                • 查询结果

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

                  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 ProductStatusFROM 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-处理多列

                    SELECT    ProductName,    UnitPrice,    StockQuantity,    CASE        WHEN StockQuantity > 50 AND UnitPrice = 30 THEN 'Popular and Expensive'        ELSE 'Limited Stock, Expensive'    END AS ProductCategoryFROM 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-加入窗口函数

                      SELECT    ProductName,    UnitPrice,    StockQuantity,    CASE        WHEN StockQuantity > AVG(StockQuantity) OVER () THEN 'Above Avg Stock'        ELSE 'Below Avg Stock'    END AS StockComparisonFROM 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：

                        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  
                        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 OtherQuantityFROM Orders oJOIN Products p ON o.ProductID = p.ProductIDGROUP 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 SalaryComparisonFROM Employees;
                         
                        4. 使用CASE WHEN进行数据分桶
                         
                        样例SQL：
                         
                        SELECT    CustomerID,    Age,    Gender,    CASE        WHEN Age = 30 AND Age  
                        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  50 THEN 'Medium'        ELSE 'Low'    END AS QuantityGroupFROM SalesGROUP BY productName;
                         
                        3. 动态排序规则
                           
                        通过在ORDER BY子句中使用CASE WHEN，您可以根据不同条件动态调整查询结果的排序规则。
                          
                        SELECT    productName,    unitPriceFROM ProductsORDER BY    CASE        WHEN category = 'Electronics' THEN unitPrice        WHEN category = 'Clothing' THEN unitPrice * 0.9        ELSE unitPrice * 1.1    END;
                         
                        4. 分位数分析
                           
                        使用CASE WHEN可以在查询结果中对数据进行分位数分析，识别哪些数据点位于不同的分位数区间。
                          
                        SELECT    productName,    unitPrice,    CASE        WHEN unitPrice