CASE函数

-> 使用类似switch-case与if-else if

-> 语法

•case [字段]

• when 表达式 then 显示数据

• else 显示数据

•end as 别名

-> then 后数据类型要一致

简单的例子:

1 select 
2     case 
3         when Sex='m' then '男'
4         when Sex='f' then '女' 
5         else '中性'
6     end as 性别
7     ,*
8 from dbo.StudentTestInsert

 1 select 
 2     case Sex
 3         when 'm' then '男'
 4         when 'f' then '女'
 5         else '中性'
 6     end as 性别
 7     ,*
 8 from Student
 9

 1 select 
 2     case 
 3         when Score >= 90 then 'A'
 4         when Score >=80  then 'B'
 5         when Score >=70  then 'C'
 6         when Score >=60  then 'D'
 7         else 'E'
 8     end as Score1
 9     ,case Score/10
10         when 10 then 'A'
11         when 9  then 'A'
12         when 8  then 'B'
13         when 7  then 'C'
14         when 6  then 'D'
15         else 'E'
16     end as Score2
17 from Score

 1 create table Score
 2 (
 3     学号 nvarchar(10),
 4     课程 nvarchar(10),
 5     成绩 int
 6 )
 7 
 8 insert into Score values('0001','语文',87);
 9 insert into Score values('0001','数学',79);
10 insert into Score values('0001','英语',95);
11 insert into Score values('0002','语文',69);
12 insert into Score values('0002','数学',84);
13 
14 
15 select 
16     学号, 
17     SUM(case when 课程='语文' then 成绩 else 0 end) as 语文, 
18     SUM(case when 课程='数学' then 成绩 else 0 end) as 数学, 
19     AVG(case when 课程='英语' then 成绩 end) as 英语 
20 from 
21     Score 
22 group by 
23     学号;

1 2 select 
3     学号, 
4     case when 课程='语文' then 成绩 else 0 end as 语文, 
5     case when 课程='数学' then 成绩 else 0 end as 数学, 
6     case when 课程='英语' then 成绩 end as 英语 
7 from 
8     Score