--简单case use Northwind go select top 10 orderID,orderID %10 AS 'Las Digit',position= case orderID % 10 when 1 then'First' when 2 then 'Second' When 3 then 'Third' When 4 then 'Fourth' else 'something else' end from Orders use Northwind go select top 10 orderid %10 AS "LAST NIGHT", Productid,"How Close?"= case orderid % 10 when productid then 'Exact Match!' when productid-1 then 'within 1' when productid+1 then 'within 1' else 'More han one apart' end from [order Details] where productID<10 order by orderID desc
--搜索case --搜索case语句和简单case 语句非常相同,只有两个不同点 --1.没有输入表达式(case和when之间) --2.when表达式必须为布尔值 use Northwind go select top 10 orderID %10 as "Last Digit", productID, "How Close?"=case when (OrderId % 10)<3 then 'Ends with less than three' when productid=6 then 'ProductId is 6' --ABS取绝对值 when ABS(orderid %10 -productid)<=1 then 'within 1' else 'Mroe than one apart' end from [Order Details] where ProductID<10 order by OrderID desc