Oracle CASE使用实战

首先来看一下CASE的使用
CASE DECODE 可以再对查询数据进行判断做出响应的结果。随着BI的兴起。CASE的应用也越来越广泛。
语法如下
CASE  WHEN 条件一
      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, DEPTNO  FROM 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, DEPTNO  FROM 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, DEPTNO  FROM EMP WHERE DEPTNO = 20  Union All SELECT EMPNO, ENAME, SAL + 1000, DEPTNO  FROM 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 , DEPTNO  FROM 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_DESCRIPTION  VARCHAR2   打印机描述



观察一下数据
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_AMT  NUMBER   实际金额
CHECK_NBR    VARCHAR2(4)    校验
ESTIMATES
ITEM_NBR              NUMBER   商品编号
ESTIMATED_AMT  NUMBER   预算






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 可以起到事半功倍的效果。