SQL中case when的用法详解

Case具有两种格式。简单Case函数和Case搜索函数。

简单Case函数

1、语法

CASE input_expression_r
WHEN when_expression_r THEN result_expression_r
[ ...n ]
[
ELSE else_result_expression_r
]
END

2、实例
 

CASE sex WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

Case搜索函数

1、语法

CASE
WHEN Boolean_expression_r THEN result_expression_r
[ ...n ]
[
ELSE else_result_expression_r
]
END
 

2、实例

CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key)

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。
 

一张表数据如下 1900-1-1 胜 1900-1-1 胜 1900-1-1 负 1900-1-2 胜 1900-1-2 胜 写出一条SQL语句，使检索结果如下:          胜  负 1900-1-1 2   1 1900-1-2 2   0 我随手建了这样一个表：

create table test(Date varchar(50) null, Result varchar(50) null)
并将上面的数据都插入到表中。

经过一番尝试和修改，终于得到了答案：

select distinct Date,
sum(case Result when '胜' then 1 else 0 end) as '胜',
sum(case Result when '负' then 1 else 0 end) as '负'
from test
group by date
这里我要说的，其实是SQL中case when的用法。它在普通的SQL语句中似乎并不常见，我本人以前也没在实际项目中使用过。遇到类似问题，往往通过代码或多条SQL语句实现。或者是如下这种丑陋的SQL，并且还伴随着很多潜在的BUG（如，当没有‘负’时）。

select a.date,a.a1 胜,b.b1 负 from
(select date,count(date) a1 from test where Result ='胜' group by date) a,
(select date,count(date) b1 from test where Result ='负' group by date) b
where a.date=b.date
我们不妨来复习一下CASE WHEN的语法。

CASE WHEN有两种用法，一种是类似上面例子中那样的简单CASE函数：

CASE result
    WHEN '胜' THEN 1
    WHEN '负' THEN 2
ELSE 0
END
还有一种是CASE搜索函数：

CASE WHEN result='胜' THEN 1
     WHEN result='负' THEN 2
ELSE 0
END
其中result='胜'可以替换为其他条件表达式。如果有多个CASE WHEN表达式符合条件，将只返回第一个符合条件的子句，其余子句将被忽略。

用CASE WHEN语句可以简化我们平时工作中遇到的很多问题。如性别在表中存的是数字1、2，但是希望查询出来男、女时，可以这样：

不用CASE WHEN 的做法。源代码如下：

string sql="select * from CONTACTPERSON ,CUSTOMER where CONTACTPERSONID='"+strContactid+"' and CONTACTPERSON.CUSTOMERID=CUSTOMER.CUSTOMERID";
DataSet ds=new DataSet();
ds=Common.GetDSCommon(sql);  

if(ds.Tables[0].Rows[0]["CONTACTPERSONSEX"].ToString()=="0")
   {
    ds.Tables[0].Rows[0]["CONTACTPERSONSEX"]="男";
   }
   else
   {
                ds.Tables[0].Rows[0]["CONTACTPERSONSEX"]="女";
   }

使用CASE WHEN做法。

string sql="select CUSTOMER.*,CONTACTPERSON.CONTACTPERSONNAME,CONTACTPERSON.CONTACTPERSONBIRTHDAY,CONTACTPERSON.CONTACTPERSONPOSITION,CONTACTPERSON.CONTACTPERSONDEPTNAME,";
   sql+="CONTACTPERSON.TELEPHONE,CONTACTPERSON.MAIL,CONTACTPERSON.CITY,CONTACTPERSON.PROVINCE,CONTACTPERSON.POSTCODE,CONTACTPERSON.COUNTRY,CONTACTPERSON.MEMO,";
   sql+="CONTACTPERSON.DIRECTOR,";
   
    sql+="case when CONTACTPERSON.CONTACTPERSONSEX = '0'then '男' when CONTACTPERSON.CONTACTPERSONSEX= '1' then '女' end as CONTACTPERSONSEX from CUSTOMER ,CONTACTPERSON  where CONTACTPERSONID='"+strContactid+"' and CONTACTPERSON.CUSTOMERID=CUSTOMER.CUSTOMERID";

可以看出这样明显加长了，SQL语句，这样做有什么好处呢？

--如果使用Case函数，SQL代码如下:

SELECT  SUM(population),

CASE country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END
FROM    Table_A
GROUP BY CASE country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END;

--同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下；

SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM    Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;