PL/SQL编程之道之CASE使用实战
Oracle CASE使用实战首先来看一下CASE的使用
CASE DECODE 可以再对查询数据进行判断做出响应的结果。随着BI的兴起。CASE的应用也越来越广泛。
语法如下
CASEWHEN 条件一
THEN 结果一
WHEN条件二
THEN 结果二
ELSE 结果三
END
CASE最常用的功能：
１ 将判断放在查询子句中合并查询。
２ 进行行列转换
３ 根据条件的不同返回不同的结果，以完成复杂逻辑判断
回复 甲骨论-晨曦 的帖子
1 同样是一个公司的人，待遇差距咋就那么大呢？
公司年度总结出来了。部门10盈利增长100%。部门20也增长了。负增长50%。
经过公司党委，工会，董事会啥的研究决定：
给部门10的员工每人涨工资1000元的工资。
给部门20的员工每人涨负200刀元工资。
现在要求先出个报表看一下调整之后的工资情况。
情况就是这么个情况。事情就是这么个事情。
我们先来看一下数据。
EMPNO ENAME SAL DEPTNO
7369 SMITH 800.00 20
7499 ALLEN 1600.00 30
7521 WARD 1250.00 30
7566 JONES 2975.00 20
7654 MARTIN 1250.00 30
7698 BLAKE 2850.00 30
7782 CLARK 2450.00 10
7788 SCOTT 3000.00 20
7839 KING 5000.00 10
7844 TURNER 1500.00 30
7876 ADAMS 1100.00 20
7900 JAMES 950.00 30
7902 FORD 3000.00 20
7934 MILLER 1300.00 10
最基本的思路
1 找出部门10的员工姓名 工资 部门编号 然后将其工资+1000
2 找出部门20的员工姓名 工资部门编号然后将其工资-500
3 两个结果集相连 一切就ＯＫ了。
1 找出部门10的员工姓名 工资 部门编号 然后将其工资+1000
SELECT EMPNO, ENAME, SAL + 1000, DEPTNOFROM EMP WHERE DEPTNO = 10
EMPNO ENAME SAL+1000 DEPTNO
7782 CLARK 3450 10
7839 KING 6000 10
7934 MILLER 2300 10
2 找出部门20的员工姓名 工资部门编号然后将其工资-500
SELECT EMPNO, ENAME, SAL -500, DEPTNOFROM EMP WHERE DEPTNO = 20
EMPNO ENAME SAL-500 DEPTNO
7369 SMITH 300 20
7566 JONES 2475 20
7788 SCOTT 2500 20
7876 ADAMS 600 20
7902 FORD 2500 20
连接起来 为了便于观察我们按照部门编号排序
SELECT EMPNO, ENAME, SAL -500, DEPTNOFROM EMP WHERE DEPTNO = 20Union All SELECT EMPNO, ENAME, SAL + 1000, DEPTNOFROM EMP WHERE DEPTNO = 10Order By deptno
EMPNO ENAME SAL-500 DEPTNO
7839 KING 6000 10
7782 CLARK 3450 10
7934 MILLER 2300 10
7902 FORD 2500 20
7788 SCOTT 2500 20
7566 JONES 2475 20
7369 SMITH 300 20
7876 ADAMS 600 20
很好很和谐。结果正确。
但是请注意 我们访问了两次EMP表。
如果用CASE呢？这个查询完全可以合并到一个查询当中去。
使用CASE 完成查询所需要考虑的事情
1 是否访问相同的表。如果访问的表不同就不用考虑了
2 是否使用相同的列。 如果是基本肯定可以合并了
如果不同的条件有组合 就需要注意业务逻辑。
3 如果使用的列不同。那么不同UNION 查询的条件是否有交集。
如果没有 基本上也可以合并。不过需要注意合并后的性能问题。
根据上面的注意事项我们来审视一下上面的例子
1 是否访问相同的表？
是
2 是否使用相同的列
也是，且没有交集。
那么开始我们的合并。
1 先找到10 20部门的员工
SELECT EMPNO, ENAME, SAL , DEPTNOFROM EMP WHERE DEPTNO In (10,20)
EMPNO ENAME SAL DEPTNO
7369 SMITH 800.00 20
7566 JONES 2975.00 20
7782 CLARK 2450.00 10
7788 SCOTT 3000.00 20
7839 KING 5000.00 10
7876 ADAMS 1100.00 20
7902 FORD 3000.00 20
7934 MILLER 1300.00 10
然后根据部门的不同调整工资。
SELECT EMPNO,
ENAME,
SAL + (CASE
WHEN DEPTNO = 10 THEN
1000
WHEN DEPTNO = 20 THEN
-500
END) SAL,
DEPTNO
FROM EMP
WHERE DEPTNO IN (10, 20)；
结果如下
EMPNO ENAME SAL DEPTNO
7934 MILLER 2300 10
7782 CLARK 3450 10
7839 KING 6000 10
7902 FORD 2500 20
7876 ADAMS 600 20
7566 JONES 2475 20
7369 SMITH 300 20
7788 SCOTT 2500 20 2 调度打印机
一个工作室有多台打印机。其中有为用户指定的打印机还有未指定用户的打印机。
如果一个用户有指定的打印机则使用指定的打印机。如果没有则使用未指定的打印机中的一台。
表如下
PrinterControl
USER_ID VARCHAR2 用户名
PRINTER_NAME VARCHAR2 打印机名
PRINTER_DESCRIPTIONVARCHAR2 打印机描述
观察一下数据
USER_ID PRINTER_NAME PRINTER_DESCRIPTION
chacha LPT1 First floor_printer
lee LPT2 Second floor_printer
thomas LPT3 Third floor_printer
LPT4 Common printer for new user
LPT5 Common printer for new user
首先看一下
有些人有固定的打印机。而另外一些人没有。
思路：
1 查看输入的i_userid 如果在表中存在则找到指定的打印机返回。
2 如果没有则返回4号 5号打印机中的一台。
CREATE OR REPLACE FUNCTION GET_PRINTER(I_USERID IN VARCHAR2)
RETURN VARCHAR2 AS
O_PRINTER VARCHAR2(6);
BEGIN
BEGIN
SELECT PRINTER_NAME
INTO O_PRINTER
FROM PRINTERCONTROL
WHERE USER_ID = I_USERID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF I_USERID >= 'k' THEN
O_PRINTER := 'LPT4';
ELSE
O_PRINTER := 'LPT5';
END IF;
END;
RETURN O_PRINTER;
END;
现在我们使用CASE语句做一下：
首先的问题就是如果输入的userid不在拥有指定打印机的那三个人中。
则会找不到数据。
这里我们可以使用聚合函数的特性来解决。
如果userid不在拥有指定打印机的那三个人中。使用聚合函数后虽然没有数据但是仍会返回一个空行。
SELECT NVL(MAX(PRINTER_NAME),
(CASE
WHEN 'kk' >= 'k' THEN
'LPT4'
WHEN 'kk' < 'k' THEN
'LPT5'
END))
FROM PRINTERCONTROL
WHERE USER_ID = 'kk'
本例中 CASE语句主要是用来做负载均衡。 3 里程碑
一个流程有三个步骤 现在需要把各个步骤的时间填到报表之中。
SERVICESSCHEDULE
SHOP_ID VARCHAR2(3) 商铺号
ORDER_NBR VARCHAR2(10) 订单号
SCH_SEQ INTEGER 步骤
SERVICE_TYPE VARCHAR2(2) 服务类型
SCH_DATE DATE 实施时间
(1 = 'processed')
(2 = 'completed')
(3 = 'confirmed')
SERVICESSCHEDULE
SHOP_ID ORDER_NBR SCH_SEQ SERVICE_TYPE SCH_DATE
002 4155526710 1 01
002 4155526710 2 01
002 4155526710 3 01
002 4155526711 1 01
002 4155526711 2 01
002 4155526711 3 01
2 4155526710 1 1 1994-7-16
2 4155526710 2 1 1994-7-30
2 4155526710 3 1 1994-10-1
2 4155526711 1 1 1994-7-16
2 4155526711 2 1 1994-7-30
2 4155526711 3 1
要求返回结果是
ORDER_NBR PROCESSED COMPLETED CONFIRMED
4155526711
4155526710
4155526711 1994-7-16 1994-7-30
4155526710 1994-7-16 1994-7-30 1994-10-1
--这是一个经典的行转列案例。
SELECT ORDER_NBR,
MAX(CASE
WHEN SCH_SEQ = 1 THEN
SCH_DATE
ELSE
NULL
END) AS PROCESSED,
MAX(CASE
WHEN SCH_SEQ = 2 THEN
SCH_DATE
ELSE
NULL
END) AS COMPLETED,
MAX(CASE
WHEN SCH_SEQ = 3 THEN
SCH_DATE
ELSE
NULL
END) AS CONFIRMED
FROM SERVICESSCHEDULE
GROUP BY ORDER_NBR, SERVICE_TYPE;
格式化报表
NAME
----------------------------------------
Al
Ben
VARCHAR2lie
David
Ed
Frank
Greg
Howard
Ida
Joe
Ken
Larry
Mike
格式化为
name1 name2 name3
========================
Al Ben VARCHAR2lie
David Ed Frank
Greg Howard Ida
Joe Ken Larry
Mike NULL NULL
或者
name1 name2 name3 name4
==============================
Al Ben VARCHAR2lie David
Ed Frank Greg Howard
Ida Joe Ken Larry
Mike NULL NULL NULL
思考：如何使用SQL 来完成 如果列数不定如何通过PL/SQL写出一个可以满足不定列的过程
思路 以三列为例
对姓名排序，别给出序号。
然后做%3运算 并做行转列
SELECT MAX(CASE
WHEN SEQ = 0 THEN
NAME
ELSE
NULL
END),
MAX(CASE
WHEN SEQ = 1 THEN
NAME
ELSE
NULL
END),
MAX(CASE
WHEN SEQ = 2 THEN
NAME
ELSE
NULL
END)
FROM (SELECT NAME,
MOD(ROW_NUMBER() OVER(ORDER BY NAME) - 1, 3) SEQ,
TRUNC((ROW_NUMBER() OVER(ORDER BY NAME) - 1) / 3) CLASS
FROM NAMES)
GROUP BY CLASS
ORDER BY CLASS 6 预算
ITEMS
ITEM_NBR NUMBER商品号
ITEM_DESCR VARCHAR2(7) 描述
ACTULS
ITEM_NBR NUMBER 商品编号
ACTUAL_AMTNUMBER 实际金额
CHECK_NBR VARCHAR2(4) 校验
ESTIMATES
ITEM_NBR NUMBER 商品编号
ESTIMATED_AMTNUMBER 预算
Items
item_nbr item_descr
=====================
10 'Item 10'
20 'Item 20'
30 'Item 30'
40 'Item 40'
50 'item 50'
Actuals
item_nbr actual_amt check_nbr
=================================
10 300.00 '1111'
20 325.00 '2222'
20 100.00 '3333'
30 525.00 '1111'
Estimates
item_nbr estimated_amt
=========================
10 300.00
10 50.00
20 325.00
20 110.00
40 25.00
需要得到的结果
item_nbr item_descr actual_tot estimate_tot check_nbr
===================================================
10 'item 10' 300.00 350.00 '1111'
20 'item 20' 425.00 435.00 'Mixed'
30 'item 30' 525.00 NULL '1111'
40 'item 40' NULL 25.00 NULL
50 'item 50' NULL NULL NULL
Item 50出现在结果中 说明item是强势表（就是外连接中不损失数据的表 暂且这么叫吧 想了半天也没想到该叫什么。）
20 'item 20' 425.00 435.00 'Mixed'
item_nbr actual_amt check_nbr
20 325.00 '2222'
20 100.00 '3333'
425 为Actuals表中两行记录金额相加
mixed 符合多个check_nbr （'2222'，'3333'）
435.为Estimates
表中两行数据相加
item_nbr estimated_amt
20 325.00
20 110.00
思路 先将Actuals汇总 根据不同的check_nbr的数量 得到check_nbr是否为Mixed
SELECT ITEM_NBR, SUM(ACTUAL_AMT) ACTUAL_TOT, (CASE WHEN COUNT(DISTINCT CHECK_NBR) != 1 THEN 'Mixed' ELSE MAX(CHECK_NBR) END) CHECK_NBR
FROM ACTUALS
GROUP BY ITEM_NBR
ITEM_NBR ACTUAL_TOT CHECK_NBR
10 300 1111
20 425 Mixed
30 525 1111
再将ESTIMATES表金额汇总
SELECT ITEM_NBR, SUM(ESTIMATED_AMT) ESTIMATE_TOT FROM ESTIMATES GROUP BY ITEM_NBR
ITEM_NBR ESTIMATE_TOT
20 435
40 25
10 350
再将这两个结果集与ITEMS做外连接 即可得到结果
SELECT ITE.ITEM_NBR, ITE.ITEM_DESCR, ACTUAL_TOT, ESTIMATE_TOT, CHECK_NBR
FROM (SELECT ITEM_NBR,
SUM(ACTUAL_AMT) ACTUAL_TOT,
(CASE
WHEN COUNT(DISTINCT CHECK_NBR) != 1 THEN
'Mixed'
ELSE
MAX(CHECK_NBR)
END) CHECK_NBR
FROM ACTUALS
GROUP BY ITEM_NBR) ACT,
(SELECT ITEM_NBR, SUM(ESTIMATED_AMT) ESTIMATE_TOT
FROM ESTIMATES
GROUP BY ITEM_NBR) EST,
ITEMS ITE
WHERE ITE.ITEM_NBR = EST.ITEM_NBR(+)
AND ITE.ITEM_NBR = ACT.ITEM_NBR(+)
ORDER BY ITEM_NBR 7 杂志
TITLES
PRODUCT_ID INTEGER 产品号
MAGAZINE_SKU INTEGER 杂志编号
ISSN INTEGER 国际期刊号
ISSN_YEAR INTEGER 日期
NEWSSTANDS
STAND_NBR INTEGER 报亭编号
STAND_NAME VARCHAR2(20) 姓名
SALES
PRODUCT_ID INTEGER 产品号
STAND_NBR INTEGER 报亭编号
NET_SOLD_QTY INTEGER 平均销售数量
每个表的数据如下
TITLES
PRODUCT_ID MAGAZINE_SKU ISSN ISSN_YEAR
1 12345 1 2006
2 2667 1 2006
3 48632 1 2006
4 1107 1 2006
5 12345 2 2006
6 2667 2 2006
7 48632 2 2006
8 1107 2 2006
SALES
PRODUCT_ID STAND_NBR NET_SOLD_QTY
1 1 1
2 1 4
3 1 1
4 1 1
5 1 1
6 1 2
7 1 1
4 2 5
8 2 6
3 2 1
1 3 1
2 3 3
4 3 1
5 3 1
6 3 3
7 3 3
1 4 1
2 4 1
3 4 4
4 4 1
5 4 1
6 4 1
7 4 2
--先得到各个每个报亭各个杂志的平均销售数量
SELECT STAND_NBR,
AVG(CASE
WHEN MAGAZINE_SKU = 2667 THEN
NET_SOLD_QTY
END) AVG_2667,
AVG(CASE
WHEN MAGAZINE_SKU = 48632 THEN
NET_SOLD_QTY
END) AVG_48632,
AVG(CASE
WHEN MAGAZINE_SKU = 1107 THEN
NET_SOLD_QTY
END) AVG_1107
FROM SALES, TITLES
WHERE SALES.PRODUCT_ID = TITLES.PRODUCT_ID
GROUP BY STAND_NBR
STAND_NBR AVG_2667 AVG_48632 AVG_1107
1 3 1 1
2 1 5.5
4 1 3 1
3 3 3 1
然后在按照条件筛选
SELECT STAND_NBR
FROM (SELECT STAND_NBR,
AVG(CASE
WHEN MAGAZINE_SKU = 2667 THEN
NET_SOLD_QTY
END) AVG_2667,
AVG(CASE
WHEN MAGAZINE_SKU = 48632 THEN
NET_SOLD_QTY
END) AVG_48632,
AVG(CASE
WHEN MAGAZINE_SKU = 1107 THEN
NET_SOLD_QTY
END) AVG_1107
FROM SALES, TITLES
WHERE SALES.PRODUCT_ID = TITLES.PRODUCT_ID
GROUP BY STAND_NBR)
WHERE AVG_1107 > 5
OR (AVG_2667 > 2 AND AVG_48632 > 2)
最终结果为
STAND_NBR
2
3
8 毕业
每个学生所有课程都达到了毕业所需最低学分才可以毕业
CATEGORIES
CREDIT_CAT VARCHAR2(1) 课程类别
RQD_CREDITS INTEGER 毕业所需最低学分
CREDITSEARNED
STUDENT_NAME VARCHAR2(10) 学生姓名
CREDIT_CAT VARCHAR2(1) 课程类别
CREDITS INTEGER 获得学分
CATEGORIES
CREDIT_CAT RQD_CREDITS
A 10
B 3
C 5
CREDITSEARNED
STUDENT_NAME CREDIT_CAT CREDITS
Joe A 3
Joe A 2
Joe A 3
Joe A 3
Joe B 3
Joe C 3
Joe C 2
Joe C 3
Bob A 2
Bob C 2
Bob A 12
Bob C 4
John A 1
John B 100
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary A 1
Mary B 1
Mary B 1
Mary B 1
Mary B 1
Mary B 1
Mary B 1
Mary B 1
Mary C 1
Mary C 1
Mary C 1
Mary C 1
Mary C 1
Mary C 1
Mary C 1
Mary C 1
思路：
按照学生姓名 课程类别 分组 获得每个学生在每个类别上获得的学分总数。
比较每个学生的总分数与规定分数。如果三科皆满则显示在毕业列中。
SELECT STUDENT_NAME,
(Case When SUM(CASE
WHEN SUM_CRE >= RQD_CREDITS THEN
1
ELSE
0
END)=3 Then 'X' Else Null End ),
(Case When SUM(CASEa
WHEN SUM_CRE >= RQD_CREDITS THEN
1
ELSE
0
END)!= 3 Then 'X' Else Null End )
FROM CATEGORIES CAT,
(SELECT STUDENT_NAME, CREDIT_CAT, SUM(CREDITS) SUM_CRE
FROM CREDITSEARNED
GROUP BY STUDENT_NAME, CREDIT_CAT) CRE
WHERE CAT.CREDIT_CAT(+) = CRE.CREDIT_CAT
GROUP BY STUDENT_NAME 9 教师
有一张反映任课教师的报表
由于考虑到一个课程可能有多个任课教师
所以报表上有两个位置来显示教师的名字
规则如下：
如果只有一个任课教师 则在第一个位置上显示任课教师的名字 第二个位置为NULL
如果有两个任课教师则按姓名升序排列
如果超过两个任课教师则 显示按姓名排序最靠前的教师姓名 第二个位置显示More
COURSE_NBR INTEGER 课程号
STUDENT_NAME VARCHAR2(10) 学生姓名
TEACHER_NAME VARCHAR2(10) 教师姓名
SELECT COURSE_NBR,
MAX(CASE
WHEN RN = 1 THEN
TEACHER_NAME
END),
MAX(CASE
WHEN RN = 2 AND CT = 1 THEN
NULL
WHEN RN = 2 AND CT = 2 THEN
TEACHER_NAME
WHEN RN = 2 AND CT > 2 THEN
'more'
END)
FROM (SELECT COURSE_NBR,
TEACHER_NAME,
COUNT(*) OVER(PARTITION BY COURSE_NBR) CT,
ROW_NUMBER() OVER(PARTITION BY COURSE_NBR ORDER BY TEACHER_NAME) RN
FROM REGISTER T)
GROUP BY COURSE_NBR
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (1, '1 ', 't10 ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (2, '2 ', 't21 ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (2, '2 ', 't22 ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (3, '3 ', 't31 ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (3, '3 ', 't32 ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (3, '3 ', 't33 '); 结帖了
总结
使用CASE 完成查询所需要考虑的事情
1 是否访问相同的表。如果访问的表不同就不用考虑了
2 是否使用相同的列。 如果是基本肯定可以合并了
如果不同的条件有组合 就需要注意业务逻辑。
3 如果使用的列不同。那么不同UNION 查询的条件是否有交集。
如果没有 基本上也可以合并。不过需要注意合并后的性能问题。
CASE 无论在连接事务处理系统或联机分析系统中都得到了广泛的应用。
在某些 情况下，通过使用CASE替代UNION 可以起到事半功倍的效果。
页:
[1]
2