东莞建网站哪家强,免费发布招聘信息,给大家推荐免费视频服务器,兰州市解封最新消息SQL中的CASE WHEN语句#xff1a;从基础到高级应用指南
准备工作 - 表1: products 示例数据#xff1a;
我们使用一个名为Products的表#xff0c;包含以下列#xff1a;ProductID、ProductName、CategoryID、UnitPrice、StockQuantity。
-- 建表
CREATE TA…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)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;-- 样例数据
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);示例展示
productIDproductNamecategoryIDunitPricestockQuantity1Laptop1800502Smartphone15001003T-shirt2202004Jeans2401505Headphones110075
一. CASE WHEN 基础使用
1. CASE WHEN-基本使用
SELECTProductName,UnitPrice,
CASEWHEN UnitPrice 100 THENExpensive ELSE Affordable END AS PriceCategory
FROMProducts;查询结果
ProductNameUnitPricePriceCategoryLaptop800ExpensiveSmartphone500ExpensiveT-shirt20AffordableJeans40AffordableHeadphones100Affordable
2. CASE WHEN-多条件
SELECTproductName,stockQuantity,
CASEWHEN stockQuantity 100 THENIn Stock WHEN stockQuantity 50 THENLimited Stock ELSE Out of Stock END AS StockStatus
FROMproducts;查询结果
productNamestockQuantityStockStatusLaptop50Out of StockSmartphone100Limited StockT-shirt200In StockJeans150In StockHeadphones75Limited Stock
3. CASE WHEN-聚合函数
SELECTcategoryID,AVG( unitPrice ) AS AvgPrice,
CASEWHEN AVG( unitPrice ) 50 THENHigh Price ELSE Low Price END AS PriceCategory
FROMproducts
GROUP BYcategoryID;查询结果
categoryIDAvgPricePriceCategory1466.6667Hign Price230low Price
4. CASE WHEN-日期条件
SELECTproductName,
CASEWHEN EXTRACT( MONTH FROM CURRENT_DATE ) 8 THEN( SELECT NOW() ) ELSE Other Month END AS CurrentTime
FROMproducts;查询结果
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-用于排序
SELECTProductName,UnitPrice,CASEWHEN UnitPrice 50 THEN ExpensiveELSE AffordableEND AS PriceCategory
FROM Products
ORDER BY 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-子查询
SELECTproductName,unitPrice,( CASE WHEN unitPrice ( SELECT AVG( unitPrice ) FROM products ) THEN Above Avg ELSE Below Avg END ) AS PriceComparison
FROMproducts;查询结果
productNameunitPricePriceComparisonLaptop800Above AvgSmartphone500Above AvgT-shirt20Below AvgJeans40Below AvgHeadphones100Below Avg
7. CASE WHEN-计算字段
SELECTProductName,UnitPrice,StockQuantity,CASEWHEN StockQuantity 0 THEN UnitPrice / StockQuantityELSE 0END AS PricePerUnit
FROM Products;查询结果
productNameunitPricestockQuantityPricePerUnitLaptop8005016Smartphone5001005T-shirt202000.1Jeans401500.2667Headphones100751.3333
8. CASE WHEN-动态列名
SELECTproductName,unitPrice,stockQuantity,
CASEWHEN stockQuantity 150 THENHigh WHEN stockQuantity 100 THENMedium ELSE Low END AS StockCategory,
CASEWHEN stockQuantity 100 THENstockQuantity * 1.1 ELSE stockQuantity * 1.05 END AS AdjustedStock
FROMproducts;查询结果
productNameunitPricestockQuantityStockCategoryAdjustedStockLaptop80050Low52.5Smartphone500100Low105T-shirt20200High220Jeans40150Medium165Headphones10075Low78.75
9. CASE WHEN-带有嵌套逻辑
SELECTProductName,UnitPrice,CASEWHEN StockQuantity 100 THENCASEWHEN UnitPrice 50 THEN High Demand, High PriceELSE High Demand, AffordableENDELSE Low DemandEND AS ProductStatus
FROM Products;查询结果
productNameunitPriceProductStatusLaptop800Low DemandSmartphone500Low DemandT-shirt20High Demand, AffordableJeans40High Demand, AffordableHeadphones100Low Demand
10. CASE WHEN-处理字符串匹配
SELECTProductName,CASEWHEN ProductName LIKE %Laptop% THEN ElectronicsWHEN ProductName LIKE %T-shirt% THEN ClothingELSE OtherEND AS Category
FROM Products;查询结果
productNameCategoryLaptopElectronicsSmartphoneOtherT-shirtClothingJeansOtherHeadphonesOther
11. CASE WHEN-用于条件合并
SELECTProductName,UnitPrice,CASEWHEN UnitPrice 50 AND StockQuantity 50 THEN High Price, High StockWHEN UnitPrice 50 OR StockQuantity 50 THEN High Price or High StockELSE Low Price and Low StockEND AS 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-处理多列
SELECTProductName,UnitPrice,StockQuantity,CASEWHEN StockQuantity 50 AND UnitPrice 30 THEN Popular and AffordableWHEN StockQuantity 50 AND UnitPrice 30 THEN Limited Stock, AffordableWHEN StockQuantity 50 AND UnitPrice 30 THEN Popular and ExpensiveELSE Limited Stock, ExpensiveEND AS ProductCategory
FROM Products;查询结果
productNameunitPricestockQuantityProductCategoryLaptop80050Limited Stock, ExpensiveSmartphone500100Popular and ExpensiveT-shirt20200Popular and AffordableJeans40150Popular and ExpensiveHeadphones10075Popular and Expensive
13. CASE WHEN-加入窗口函数
SELECTProductName,UnitPrice,StockQuantity,CASEWHEN StockQuantity AVG(StockQuantity) OVER () THEN Above Avg StockELSE Below Avg StockEND AS StockComparison
FROM Products;查询结果
productNameunitPricestockQuantityStockComparisonT-shirt20200Above Avg StockLaptop80050Below Avg StockJeans40150Above Avg StockSmartphone500100Below Avg StockHeadphones10075Below 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 IncreasedWHEN s.QuantitySold LAG(s.QuantitySold) OVER (PARTITION BY p.ProductID ORDER BY s.SaleDate) THEN DecreasedELSE StableEND 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 HighWHEN Salary 60000 THEN MediumWHEN Salary 50000 THEN LowELSE Very LowEND AS SalaryLevel,CASEWHEN Salary 60000 THEN Above AverageELSE Below AverageEND AS SalaryComparison
FROM Employees;4. 使用CASE WHEN进行数据分桶
样例SQL
SELECTCustomerID,Age,Gender,CASEWHEN Age 30 THEN YoungWHEN Age 30 AND Age 40 THEN Middle-agedELSE SeniorEND AS AgeGroup,CASEWHEN Gender Male THEN MaleWHEN Gender Female THEN FemaleELSE OtherEND 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 HighWHEN SUM(quantity) 50 THEN MediumELSE LowEND 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 Q1WHEN unitPrice PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY unitPrice) THEN Q2WHEN unitPrice PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY unitPrice) THEN Q3ELSE Q4END 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 WinterWHEN EXTRACT(MONTH FROM orderDate) IN (3, 4, 5) THEN SpringWHEN EXTRACT(MONTH FROM orderDate) IN (6, 7, 8) THEN SummerELSE FallEND AS Season
FROM Orders;7. 业务阶段分析 使用CASE WHEN可以根据特定业务规则判断数据所处的不同阶段如用户生命周期阶段、订单处理阶段等。 SELECTuserID,registrationDate,CASEWHEN NOW() - registrationDate INTERVAL 30 days THEN New UserWHEN NOW() - registrationDate INTERVAL 90 days THEN Regular UserELSE Inactive UserEND 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 NegativeWHEN orderAmount 10000 THEN High AmountELSE NormalEND AS DataQuality
FROM Orders;10. 数据格式转换 使用CASE WHEN可以在不同的数据格式之间进行转换例如将布尔值转换为文本标签。 SELECTproductID,productName,inStock,CASEWHEN inStock THEN AvailableELSE Out of StockEND AS StockStatus
FROM Products;
文章转载自： http://www.morning.wbysj.cn.gov.cn.wbysj.cn http://www.morning.qdcpn.cn.gov.cn.qdcpn.cn http://www.morning.lgsfb.cn.gov.cn.lgsfb.cn http://www.morning.rxdsq.cn.gov.cn.rxdsq.cn http://www.morning.zqkr.cn.gov.cn.zqkr.cn http://www.morning.wwgpy.cn.gov.cn.wwgpy.cn http://www.morning.jypqx.cn.gov.cn.jypqx.cn http://www.morning.symgk.cn.gov.cn.symgk.cn http://www.morning.xcnwf.cn.gov.cn.xcnwf.cn http://www.morning.hlzpb.cn.gov.cn.hlzpb.cn http://www.morning.tbqxh.cn.gov.cn.tbqxh.cn http://www.morning.pkpqh.cn.gov.cn.pkpqh.cn http://www.morning.pylpd.cn.gov.cn.pylpd.cn http://www.morning.fykrm.cn.gov.cn.fykrm.cn http://www.morning.wfttq.cn.gov.cn.wfttq.cn http://www.morning.qrsrs.cn.gov.cn.qrsrs.cn http://www.morning.jcbjy.cn.gov.cn.jcbjy.cn http://www.morning.xbtlt.cn.gov.cn.xbtlt.cn http://www.morning.lnsnyc.com.gov.cn.lnsnyc.com http://www.morning.vjwkb.cn.gov.cn.vjwkb.cn http://www.morning.qsy37.cn.gov.cn.qsy37.cn http://www.morning.pbwcq.cn.gov.cn.pbwcq.cn http://www.morning.yzsdp.cn.gov.cn.yzsdp.cn http://www.morning.nfmlt.cn.gov.cn.nfmlt.cn http://www.morning.fmgwx.cn.gov.cn.fmgwx.cn http://www.morning.jngdh.cn.gov.cn.jngdh.cn http://www.morning.tpnxr.cn.gov.cn.tpnxr.cn http://www.morning.lznfl.cn.gov.cn.lznfl.cn http://www.morning.rbkl.cn.gov.cn.rbkl.cn http://www.morning.gxwyr.cn.gov.cn.gxwyr.cn http://www.morning.mztyh.cn.gov.cn.mztyh.cn http://www.morning.srbl.cn.gov.cn.srbl.cn http://www.morning.zgztn.cn.gov.cn.zgztn.cn http://www.morning.mfbzr.cn.gov.cn.mfbzr.cn http://www.morning.czgtt.cn.gov.cn.czgtt.cn http://www.morning.qnbsx.cn.gov.cn.qnbsx.cn http://www.morning.fgrcd.cn.gov.cn.fgrcd.cn http://www.morning.gnhsg.cn.gov.cn.gnhsg.cn http://www.morning.pjyrl.cn.gov.cn.pjyrl.cn http://www.morning.qbrdg.cn.gov.cn.qbrdg.cn http://www.morning.lmtbl.cn.gov.cn.lmtbl.cn http://www.morning.gtmgl.cn.gov.cn.gtmgl.cn http://www.morning.5-73.com.gov.cn.5-73.com http://www.morning.ryglh.cn.gov.cn.ryglh.cn http://www.morning.wbxr.cn.gov.cn.wbxr.cn http://www.morning.lhzqn.cn.gov.cn.lhzqn.cn http://www.morning.rhnn.cn.gov.cn.rhnn.cn http://www.morning.mcjrf.cn.gov.cn.mcjrf.cn http://www.morning.dskmq.cn.gov.cn.dskmq.cn http://www.morning.wtrjq.cn.gov.cn.wtrjq.cn http://www.morning.lwlnw.cn.gov.cn.lwlnw.cn http://www.morning.nfbxgtj.com.gov.cn.nfbxgtj.com http://www.morning.xhpnp.cn.gov.cn.xhpnp.cn http://www.morning.ylph.cn.gov.cn.ylph.cn http://www.morning.wjxyg.cn.gov.cn.wjxyg.cn http://www.morning.rzmlc.cn.gov.cn.rzmlc.cn http://www.morning.mjats.com.gov.cn.mjats.com http://www.morning.qbrdg.cn.gov.cn.qbrdg.cn http://www.morning.ljxxl.cn.gov.cn.ljxxl.cn http://www.morning.ygpdm.cn.gov.cn.ygpdm.cn http://www.morning.kxxld.cn.gov.cn.kxxld.cn http://www.morning.yznsx.cn.gov.cn.yznsx.cn http://www.morning.xnltz.cn.gov.cn.xnltz.cn http://www.morning.nzlsm.cn.gov.cn.nzlsm.cn http://www.morning.mjzgg.cn.gov.cn.mjzgg.cn http://www.morning.fkmyq.cn.gov.cn.fkmyq.cn http://www.morning.rbgqn.cn.gov.cn.rbgqn.cn http://www.morning.zkgpg.cn.gov.cn.zkgpg.cn http://www.morning.znpyw.cn.gov.cn.znpyw.cn http://www.morning.jwfqq.cn.gov.cn.jwfqq.cn http://www.morning.nrzkg.cn.gov.cn.nrzkg.cn http://www.morning.wiitw.com.gov.cn.wiitw.com http://www.morning.jtrqn.cn.gov.cn.jtrqn.cn http://www.morning.plqqn.cn.gov.cn.plqqn.cn http://www.morning.ztfzm.cn.gov.cn.ztfzm.cn http://www.morning.hblkq.cn.gov.cn.hblkq.cn http://www.morning.rtzd.cn.gov.cn.rtzd.cn http://www.morning.tkyry.cn.gov.cn.tkyry.cn http://www.morning.dbphz.cn.gov.cn.dbphz.cn http://www.morning.cffwm.cn.gov.cn.cffwm.cn
