Простое CASE Expession
CASE expression
WHEN expression1 THEN exression1
[[WHEN expression2 THEN expression2[..]]
[ELSE expressionN]
END
CASE Expression с поиском
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2[..]]
[ELSE expressionN]
END
Функции, полезные для CASEобразия:
COALESCE(expression1,expression2,...)
NULLIF(expression1,expression2)
ISNULL(expression1, expression)
COALESCE возвращает первое не-NULL выражение из списка, NULLIF возвращает NULL, если два выражения равны, ISNULL возвращает expression2 в том случае, если expression1 is null
Примеры
SELECT Category=
CASE type
WHEN popular_comp THEN Popular Computing
WHEN mod_cook THEN Modern Cooking
WHEN business THEN Businness
WHEN psyhology THEN Psyhology
WHEN trad_cook THEN Traditional Cooking
ELSE Not yet categorized
END,
Shortended Tiitle = CONVERT(varchar(30), title),
Price = price
FROM titles
WHERE price IS NOT NULL
ORDER BY type
COMPUTE AVG(price) BY TYPE
go
Category Shortedned Title Price
---------------------- ------------------------------ -----------
Business Cooking with Computers: Surrep 11.95
Business Straight Talkk About Computers 19.99
Business The Busy Executive s Database 19.99
Business You Can Combat Computer Stress 2.99
avg
============
13.73