为什么要使用CASE?
当我们的代码中需要根据从数据库中取出的某个变量的值来进行逻辑判断的话 我们可以执行先执行一边SQL 得到需要的变量的值 并且用高级编程语言进行逻辑分支判断
但是更简单的方法就是直接在SQL查询中使用CASE 它就相当于SQL中的CASE SWITCH 或者 IF THEN ELSE
基本语法:
CASE WHEN CONDITION1 THEN ACTION1
ELSE ACTION2 END
CASE 后面如果跟一个变量的话 这时它就相当于CASE SWITCH
1.CASE可以用在SELECT语句中 不包含逻辑判断 只有给出一个值 用在等号 = 后面
这段代码的意思在于根据Productline值的不同 赋给Category不同的值 此时CASE的用法相当于Switch
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
2.CASE用在SELECT中更复杂的一种情况 包含逻辑判断
CASE后面不再跟变量 而是WHEN后面跟一个逻辑表达式
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
3.在ORDER BY 语句中使用CASE
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
ELSE CountryRegionName END;
4.在HAVING语句中使用CASE
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = 'F'
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;