sql server SELECT IF 查询语句 sql server if函数

SQL Server中的判断语句：

case  判断对象  when   判断条件   then   条件为真的场合返回的结果 
else  其他的场合返回的结果
end

CASE 的语法。在一般的 SELECT 中，其语法如下：

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

例如:

case   Column1
        when  '1'    then   '男'
        when  '2'    then   '女'
 else  '其他'end

 

也可以是这个样子：

case 
       when RIGHT(b.salesDate,2)='01' or RIGHT(b.salesDate,2)='02' or RIGHT(b.salesDate,2)='03' then left(b.salesDate,4)+'Q1'
       when RIGHT(b.salesDate,2)='04' or RIGHT(b.salesDate,2)='05' or RIGHT(b.salesDate,2)='06' then left(b.salesDate,4)+'Q2'
       when RIGHT(b.salesDate,2)='07' or RIGHT(b.salesDate,2)='08' or RIGHT(b.salesDate,2)='09' then left(b.salesDate,4)+'Q3'
       when RIGHT(b.salesDate,2)='10' or RIGHT(b.salesDate,2)='11' or RIGHT(b.salesDate,2)='12' then left(b.salesDate,4)+'Q4'
 end

 

在Access中没有 case when 语句,对应的是 IIF() 函数

IIF(判断条件, 条件为真的场合返回的结果, 条件为假的场合返回的结果)

例如:

SELECT iif( isnull(Column1),2,Column1 ) FROM TableName