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