Выражения CASE

Tsql теория > Выражения CASE
17.04.2017 16:13:29


Наиболее часто встречающиеся слова в статье:

[expression1] [expression2] [Business] [expression] [expressionN] [Cooking''] [Computers] [Простое] [Expession CASE] [exression1]


Статья:

Простое 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