sql中的case应用
/*Case具有两种格式。简单Case函数和Case搜索函数。
以具体实例来表现这两种方法。其中还设计到了一些行列互换显示
的技巧.以下脚本全部在oracle 10g中测试，没有任何问题。
*/
--创建表
/*id，姓名，性别，课程，成绩*/
create table fzq
(
id varchar(4),
name varchar(5),
sex varchar(1),
kecheng varchar(8),
chengji varchar(3)
);
--向数据表中插入数据
insert into fzq
values ('1','a','1','语文','80');
insert into fzq
values ('2','b','1','语文','70');
insert into fzq
values ('3','c','0','语文','80');
insert into fzq
values ('4','d','1','语文','90');
insert into fzq
values ('5','e','0','语文','40');
insert into fzq
values ('6','f','1','语文','60');
insert into fzq
values ('7','g','1','语文','95');
insert into fzq
values ('8','a','1','数学','80');
insert into fzq
values ('9','b','1','数学','70');
insert into fzq
values ('10','c','0','数学','80');
insert into fzq
values ('11','d','1','数学','90');
insert into fzq
values ('12','e','0','数学','40');
insert into fzq
values ('13','f','1','数学','60');
insert into fzq
values ('14','g','1','数学','95');
insert into fzq
values ('15','a','1','英语','40');
insert into fzq
values ('16','b','1','英语','90');
insert into fzq
values ('17','c','0','英语','80');
insert into fzq
values ('18','d','1','英语','65');
insert into fzq
values ('19','e','0','英语','80');
insert into fzq
values ('20','f','1','英语','94');
insert into fzq
values ('21','g','1','英语','80');
--查询数据
select * from fzq;
/*
IDNAMESEXKECHENGCHENGJI
1a1语文80
2b1语文70
3c0语文80
4d1语文90
5e0语文40
6f1语文60
7g1语文95
8a1数学80
9b1数学70
10c0数学80
11d1数学90
12e0数学40
13f1数学60
14g1数学95
15a1英语40
16b1英语90
17c0英语80
18d1英语65
19e0英语80
20f1英语94
21g1英语80
*/
--case的用法作为简单Case函数
--查询学生姓名和性别（不包括重复的记录）
/*
姓名性别
a男
b男
c女
d男
e女
f男
g男
*/
SELECT distinct(NAME) 姓名, CASE sex
WHEN '1'
THEN '男'
WHEN '0'
THEN '女'
ELSE '其它'
END 性别
FROM fzq
order by name;
--显示每个学科的成绩
/*姓名语文数学英语
a 8080 40
b 7070 90
c 8080 80
d 9090 65
e 4040 80
f 6060 94
g 9595 80
*/
SELECT distinct(NAME) 姓名,
max( case kecheng
when '语文'
then chengji
end) 语文,
max( case kecheng
when '数学'
then chengji
end )数学,
max( case kecheng
when '英语'
then chengji
end) 英语
FROM fzq
group by name
order by name;
--显示每个学科的成绩,平均分和总成绩
/*
姓名语文数学英语平均分总成绩
a 8080 4066.67200
b 7070 9076.67230
c 8080 8080 240
d 9090 6581.67245
e 4040 8053.33160
f 6060 9471.33214
g 9595 8090 270
*/
SELECT distinct(NAME) 姓名,
max( case kecheng
when '语文'
then chengji
end) 语文,
max( case kecheng
when '数学'
then chengji
end )数学,
max( case kecheng
when '英语'
then chengji
end) 英语,
cast(avg(chengji) as number(10,2)) 平均分,
sum(chengji) 总成绩
FROM fzq
group by name
order by name;
--查询每个课程的男女人数，平均分和总成绩。
/*
课程性别人数平均分总成绩
数学男 579 395
数学女 260 120
英语男 573.8 369
英语女 280 160
语文男 579 395
语文女 260 120
*/
SELECT kecheng 课程,CASE sex
WHEN '1'
THEN '男'
WHEN '0'
THEN '女'
ELSE '其它'
END 性别,
count(*) 人数,
cast(avg(chengji) as number(10,2)) 平均分,
sum(chengji) 总成绩
FROM fzq
group by kecheng, CASE sex
WHEN '1'
THEN '男'
WHEN '0'
THEN '女'
ELSE '其它'
END
order by kecheng
--case的另一用用法作为搜索函数使用
--给每个学生每个课程一个评价
/*
姓名课程评价
a 数学可
a 英语不及格
a 语文可
b 数学差
b 英语良
b 语文差
c 数学可
c 英语可
c 语文可
d 数学良
d 英语差
d 语文良
e 数学不及格
e 英语可
e 语文不及格
f 数学差
f 英语优
f 语文差
g 数学优
g 英语可
g 语文优
*/
SELECT NAME 姓名,kecheng 课程, CASE
WHEN chengji<60
THEN '不及格'
WHEN chengji>=60 and chengji<=70
THEN '差'
WHEN chengji>70 and chengji<=80
THEN '可'
WHEN chengji>80 and chengji<=90
THEN '良'
WHEN chengji>90
THEN '优'
END 评价
FROM fzq
order by name,kecheng;
