1.case表达式写法
-- 简单 CASE 表达式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- 搜索 CASE 表达式
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
2.注意事项
2.1 统一各分支返回的数据类型
CASE 表达式里各个分支返回的数据类型是否一致。某个分支返回字符型，而其他
分支返回数值型的写法是不正确的。
2.2 注意 不要忘了写END
2.3养成写else的习惯
与 END 不同， ELSE 子句是可选的，不写也不会出错。不写 ELSE 子句时，CASE 表达式的执行结果是 NULL。但是不写可能会造成“语法没有错误，结 果却不对”这种不易追查原因的麻烦，所以最好明确地写上 ELSE 子句（即便是在结果可以为 NULL 的情况下）。养成这样的习惯后，我们从代码上就可以清楚地看到这种条件下会生成 NULL，而且将来代码有修改时也能减少失误。
3.例子
3.1 例子1
/* 将已有编号方式转换为新的方式并统计 */
CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
population INTEGER NOT NULL);
INSERT INTO PopTbl VALUES('杭州', 100);
INSERT INTO PopTbl VALUES('湖州', 200);
INSERT INTO PopTbl VALUES('金华', 150);
INSERT INTO PopTbl VALUES('衢州', 200);
INSERT INTO PopTbl VALUES('宁波', 300);
INSERT INTO PopTbl VALUES('深圳', 100);
INSERT INTO PopTbl VALUES('广州', 200);
INSERT INTO PopTbl VALUES('东莞', 400);
INSERT INTO PopTbl VALUES('长沙', 50);
/*在group by 后面加select后的别名是违反标准 SQL 的规则的，
因为 GROUP BY 子句比 SELECT 语句先执行，所以在 GROUP BY 子句中引
用在 SELECT 子句里定义的别称是不被允许的。事实上，在 Oracle、 DB2、
SQL Server 等数据库里采用这种写法时就会出错。
不过也有支持这种 SQL 语句的数据库，例如在 PostgreSQL 和 MySQL
中，这个查询语句就可以顺利执行。这是因为，这些数据库在执行查询语
句时，会先对 SELECT 子句里的列表进行扫描，并对列进行计算。*/
SELECT (case when city in('杭州','湖州','金华','衢州','宁波') then '浙江'
when city in('深圳','广州','东莞') then '广东'
else '湖南' end) 地区名,sum(population) as 人口
from poptbl
group by 地区名
3.2例子2 交叉表
/* 将“行结构”的数据转换成了“列结构”的数据。除了 SUM， COUNT、 AVG 等聚合函数也都可以用于将行结构的数据转换成列结构的数据。 */
CREATE TABLE PopTbl2
(city VARCHAR(32),
sex CHAR(1) NOT NULL,
population INTEGER NOT NULL,
PRIMARY KEY(city, sex));
INSERT INTO PopTbl2 VALUES('杭州', '1', 60 );
INSERT INTO PopTbl2 VALUES('杭州', '2', 40 );
INSERT INTO PopTbl2 VALUES('湖州', '1', 100);
INSERT INTO PopTbl2 VALUES('湖州', '2', 100);
INSERT INTO PopTbl2 VALUES('金华', '1', 100);
INSERT INTO PopTbl2 VALUES('金华', '2', 50 );
INSERT INTO PopTbl2 VALUES('衢州', '1', 100);
INSERT INTO PopTbl2 VALUES('衢州', '2', 100);
INSERT INTO PopTbl2 VALUES('宁波', '1', 100);
INSERT INTO PopTbl2 VALUES('宁波', '2', 200);
INSERT INTO PopTbl2 VALUES('深圳', '1', 20 );
INSERT INTO PopTbl2 VALUES('深圳', '2', 80 );
INSERT INTO PopTbl2 VALUES('广州', '1', 125);
INSERT INTO PopTbl2 VALUES('广州', '2', 125);
INSERT INTO PopTbl2 VALUES('东莞', '1', 250);
INSERT INTO PopTbl2 VALUES('东莞', '2', 150);
INSERT INTO PopTbl2 VALUES('长沙', '1', 250);
INSERT INTO PopTbl2 VALUES('长沙', '2', 150);
select city,sum(case when sex='1' then population else 0 end) '男',
sum(case when sex='2' then population else 0 end) '女'
from poptbl2
group by city;
select case when sex=1 then '男' else '女' end as sex,
sum(population) '全国',
sum(case when city in ('杭州','金华','湖州','宁波','衢州') then population else 0 end) '浙江',
sum(case when city in ('深圳','东莞','广州') then population else 0 end) '广东',
sum(case when city ='长沙' then population else 0 end)'湖南'
from poptbl2
group by sex
/*在 CASE 表达式里，我们可以使用 BETWEEN、 LIKE 和 <、 > 等
便利的谓词组合，以及能嵌套子查询的 IN 和 EXISTS 谓词。*/
/* 表之间的数据匹配 */
CREATE TABLE CourseMaster
(course_id INTEGER PRIMARY KEY,
course_name VARCHAR(32) NOT NULL);
INSERT INTO CourseMaster VALUES(1, '会计入门');
INSERT INTO CourseMaster VALUES(2, '财务知识');
INSERT INTO CourseMaster VALUES(3, '簿记考试');
INSERT INTO CourseMaster VALUES(4, '税务师');
CREATE TABLE OpenCourses
(month INTEGER ,
course_id INTEGER ,
PRIMARY KEY(month, course_id));
INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);
-- 表的匹配：使用inner join
select a.course_name,
sum(case when b.month=200706 then 1 else 0 end) '6月',
sum(case when b.month=200707 then 1 else 0 end) '7月',
sum(case when b.month=200708 then 1 else 0 end) '8月'
from coursemaster a
inner join opencourses b
on a.course_id=b.course_id
group by a.course_name
/*无论使用 IN 还是 EXISTS，得到的结果是一样的，但从性能方面来说，
EXISTS 更好。通过 EXISTS 进行的子查询能够用到“month, course_
id”这样的主键索引，因此尤其是当表 OpenCourses 里数据比较多的时候
更有优势*/
-- 表的匹配 ：使用 IN 谓词
SELECT course_name,
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200706) THEN '○'
ELSE '×' END AS "6 月",
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200707) THEN '○'
ELSE '×' END AS "7 月",
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200708) THEN '○'
ELSE '×' END AS "8 月"
FROM CourseMaster;
-- 表的匹配 ：使用 EXISTS 谓词
SELECT CM.course_name,
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND OC.course_id = CM.course_id) THEN '○'
ELSE '×' END AS "6 月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND OC.course_id = CM.course_id) THEN '○'
ELSE '×' END AS "7 月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND OC.course_id = CM.course_id) THEN '○'
ELSE '×' END AS "8 月"
FROM CourseMaster CM;
例3:在case中使用聚合函数
/* 在CASE表达式中使用聚合函数 */
CREATE TABLE StudentClub
(std_id INTEGER,
club_id INTEGER,
club_name VARCHAR(32),
main_club_flg CHAR(1),
PRIMARY KEY (std_id, club_id));
INSERT INTO StudentClub VALUES(100, 1, '棒球', 'Y');
INSERT INTO StudentClub VALUES(100, 2, '管弦乐', 'N');
INSERT INTO StudentClub VALUES(200, 2, '管弦乐', 'N');
INSERT INTO StudentClub VALUES(200, 3, '羽毛球','Y');
INSERT INTO StudentClub VALUES(200, 4, '足球', 'N');
INSERT INTO StudentClub VALUES(300, 4, '足球', 'N');
INSERT INTO StudentClub VALUES(400, 5, '游泳', 'N');
INSERT INTO StudentClub VALUES(500, 6, '围棋', 'N');
--1. 获取只加入了一个社团的学生的社团 ID。
select std_id,club_name,club_id
from studentclub
group by std_id
having count(std_id)=1
--2. 获取加入了多个社团的学生的主社团 ID。
select a.std_id,a.club_id
from
(select std_id,club_name,club_id,main_club_flg
from studentclub
order by std_id,main_club_flg desc) a
group by a.std_id
having count(a.std_id)>1
--同时获取以上的两条要求
SELECT std_id,
CASE WHEN COUNT(*) = 1 -- 只加入了一个社团的学生
THEN MAX(club_id)
ELSE MAX(CASE WHEN main_club_flg = 'Y'
THEN club_id
ELSE NULL END)
END AS main_club,
case when count(*) = 1
then '只加入了一个社区'
else '加入了多个社团' end as '参加几个社团'
FROM StudentClub
GROUP BY std_id
4.练习题
/* 练习题1-1：多列数据的最大值（练习题1-1-3也会用到） */
CREATE TABLE Greatests(keyy CHAR(1) PRIMARY KEY,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
z INTEGER NOT NULL);
INSERT INTO Greatests VALUES('A', 1, 2, 3);
INSERT INTO Greatests VALUES('B', 5, 5, 2);
INSERT INTO Greatests VALUES('C', 4, 7, 1);
INSERT INTO Greatests VALUES('D', 3, 3, 8);
#练习1-1-1，找到x,y,z三列中的最大值
##思路一，将列转化为行
select a.keyy,max(a.ok)
from
(select keyy,x ok
from greatests
union all
select keyy,y ok
from greatests
union all
select keyy,z ok
from greatests) a
group by a.keyy
##思路二，直接用case
/* 求x、y和z中的最大值 */
SELECT key,
CASE WHEN CASE WHEN x < y THEN y ELSE x END < z
THEN z
ELSE CASE WHEN x < y THEN y ELSE x END
END AS greatest
FROM Greatests;
网友评论