CASE WHEN 详解+实例

有时数据库中为了存储空间的节约，存了一些比较小的字段，这是可以在select语句中进行转换。

 

在select 语句中用条件语句：如果，数据库中一个字段Gender来存储性别，存的是1/0两个值，这时可以用selectcasewhen gender=1 then'Man'else'Woman'endas [gender] from PersonInfo

 

SQL CASE 表达式是一种通用的条件表达式，类似于其它语言中的 if/else 语句。

CASE WHEN <tt><em>condition</em></tt> THEN <tt><em>result</em></tt>
     [WHEN ...]
     [ELSE <tt><em>result</em></tt>]
END

CASE 子句可以用于任何表达式可以有效存在的地方。 condition 是一个返回boolean 的表达式。 如果结果为真，那么 CASE 表达式的结果就是符合条件的 result。 如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句。 如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值。 如果省略了 ELSE 子句而且没有匹配的条件， 结果为 NULL。

例子：

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
	    WHEN a=2 THEN 'two'
	    ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

所有 result 表达式的数据的类型都必须可以转换成单一的输出类型。

下面这个"简单的" CASE 表达式是上面的通用形式的一个特殊的变种。

CASE <tt><em>expression</em></tt>
    WHEN <tt><em>value</em></tt> THEN <tt><em>result</em></tt>
    [WHEN ...]
    [ELSE <tt><em>result</em></tt>]
END

先计算 expression 的值， 然后与所有在WHEN 子句里声明的 value 对比，直到找到一个相等的。 如果没有找到匹配的，则返回在 ELSE 子句里的 result （或者 NULL）。 这个类似于 C 里的 switch 语句。

上面的例子可以用简单 CASE 语法来写：

SELECT a,
       CASE a WHEN 1 THEN 'one'
	      WHEN 2 THEN 'two'
	      ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

CASE 表达式并不计算任何对于判断结果并不需要的子表达式。 比如，下面是一个可以避免被零除的方法：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

原文地址：http://blog.csdn.net/jiwenyi163/article/details/2794038

以下是case when 实例：

SELECT     dbo.FM_InHospital_Fee.EMPI_ID AS p_main_id, dbo.FM_InHospital_Fee.Inhosp_No AS p_settlement_code, dbo.FM_InHospital_Fee.Pat_Name AS p_name, 
                      dbo.PA_Patient_Information.Sex_Name AS p_sex, dbo.FM_InHospital_Fee.Inhosp_Index_No AS p_admit_id, 
                      dbo.PA_Inhospital_Registration.MR_No AS p_medical_record_id, dbo.FM_InHospital_Fee.Curr_Dept_Code AS p_admit_dept_code, 
                      dbo.FM_InHospital_Fee.Curr_Dept_Name AS p_admit_dept_name, dbo.PA_Inhospital_Registration.Admit_Bed_No AS p_admit_bed_no, 
                      dbo.PA_Inhospital_Registration.Admit_Date AS p_admit_date, dbo.PA_Inhospital_Registration.Discharge_Date AS p_discharge_date, '' AS p_admit_days, 
                      dbo.FM_InHospital_Fee.Medicare_Categ_Name AS p_medicare_type, dbo.PA_Patient_Information.HC_No AS p_social_security_id, 
                      dbo.FM_InHospital_Fee.Charge_Date AS p_settlement_date, dbo.FM_InHospital_Fee.Settlement_Staff_Name AS p_settlement_person, '' AS p_personal_fee, 
                      '' AS p_medicare_fund_payment, '' AS p_accept_fee, '' AS p_pay_fee, '' AS p_expect_fee, CASE WHEN Charge_Item_Code = 'BILL03' THEN 'Receivable_Fee' ELSE NULL
                       END AS p_western_medicine_fee, CASE WHEN Charge_Item_Code = 'BILL04' THEN 'Receivable_Fee' ELSE NULL END AS p_chinese_patent_medicine_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL05' THEN 'Receivable_Fee' ELSE NULL END AS p_chinese_herbal_medicine_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL06' THEN 'Receivable_Fee' ELSE NULL END AS p_inspect_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL08' THEN 'Receivable_Fee' ELSE NULL END AS p_irradiation_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL10' THEN 'Receivable_Fee' ELSE NULL END AS p_assay_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL07' THEN 'Receivable_Fee' ELSE NULL END AS p_treatment_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL09' THEN 'Receivable_Fee' ELSE NULL END AS p_operation_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL11' THEN 'Receivable_Fee' ELSE NULL END AS p_oxygen_therapy_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL01' THEN 'Receivable_Fee' ELSE NULL END AS p_medical_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL02' THEN 'Receivable_Fee' ELSE NULL END AS p_bed_fee, 
                      CASE WHEN Charge_Item_Code = 'BILL12' THEN 'Receivable_Fee' ELSE NULL END AS p_other_fee, '' AS p_material_fee, '' AS p_intensive_care_fee, 
                      '' AS p_transfusion_fee, '' AS p_nurse_fee, dbo.FM_InHospital_Fee.Total_Fee AS p_add_fee
FROM         dbo.FM_InHospital_Fee INNER JOIN
                      dbo.PA_Patient_Information ON dbo.FM_InHospital_Fee.EMPI_ID = dbo.PA_Patient_Information.EMPI_ID INNER JOIN
                      dbo.PA_Inhospital_Registration ON dbo.FM_InHospital_Fee.EMPI_ID = dbo.PA_Inhospital_Registration.EMPI_ID