Case When

select ……,case tb_case.IsSendEmail when 1 then '是' else  '否' end as Email
from 表名
where ？？=？？

 

 

SELECT <myColumnSpec> =           

CASE            

WHEN <A> THEN <somethingA>            

WHEN <B> THEN <somethingB>           

 ELSE <somethingE>            

END

USE pubs            
GO           
 SELECT          
  Title,           
 'Price Range' =           
 CASE            
WHEN price IS NULL THEN 'Unpriced'           
 WHEN price < 10 THEN 'Bargain'            
WHEN price BETWEEN 10 and 20 THEN 'Average'            
ELSE 'Gift to impress relatives'            
END            FROM titles            
ORDER BY price            GO
 
 
 
GROUP BY 子句中的 CASE： 
 
SELECT 'Number of Titles', Count(*)            
FROM titles            GROUP BY           
 CASE            WHEN price IS NULL THEN 'Unpriced'           
 WHEN price < 10 THEN 'Bargain'           
 WHEN price BETWEEN 10 and 20 THEN 'Average'            
ELSE 'Gift to impress relatives'           
 END            GO
 
 
添加一个 ORDER BY 子句，如下所示：
USE pubs            GO            
SELECT            CASE            
WHEN price IS NULL THEN 'Unpriced'            
WHEN price < 10 THEN 'Bargain'            
WHEN price BETWEEN 10 and 20 THEN 'Average'           
 ELSE 'Gift to impress relatives'            
END AS Range,            Title            
FROM titles            GROUP BY            
CASE            WHEN price IS NULL THEN 'Unpriced'            
WHEN price < 10 THEN 'Bargain'            
WHEN price BETWEEN 10 and 20 THEN 'Average'            
ELSE 'Gift to impress relatives'            END,           
 Title            ORDER BY            CASE           
 WHEN price IS NULL THEN 'Unpriced'            
WHEN price < 10 THEN 'Bargain'            
WHEN price BETWEEN 10 and 20 THEN 'Average'           
 ELSE 'Gift to impress relatives'            
END,            Title            GO
注意，为了在 GROUP BY 块中使用 CASE，查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。 
除了选择自定义字段之外，在很多情况下 CASE 都非常有用。再深入一步，你还可以得到你以前认为不可能得到的分组排序结果集。