使用 case when可以实现:等值转换、范围转换、列转行等操作。
基本格式如下:
1.简单Case函数: CASE [COLUMN_NAME] WHEN ['条件参数'] THEN ['COLUMN_NAME/显示内容'] WHEN ['条件参数'] THEN ['COLUMN_NAME/显示内容'] ...... ELSE ['COLUMN_NAME/显示内容'] END2.Case搜索函数: CASE WHEN [COLUMN_NAME] = ['COLUMN_NAME/显示内容/表达式'] THEN [''COLUMN_NAME/显示内容''] WHEN [COLUMN_NAME] = ['COLUMN_NAME/显示内容/表达式'] THEN [''COLUMN_NAME/显示内容''] ...... ELSE ['COLUMN_NAME/显示内容'] END
示例如下:
1、
SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 ELSE NULL END) 男生数, COUNT (CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数FROM students
2、
SELECT CASE WHEN 补贴类型 = '1' THEN '新吸纳劳动者就业' WHEN 补贴类型 = '2' THEN '受疫情影响的企业'ELSE '其他' END AS 类型FROM [dbo].[以工代训人员]