Oracle CASE使用实战

首先来看一下CASE的使用
CASE DECODE 可以再对查询数据进行判断做出响应的结果。随着BI的兴起。CASE的应用也越来越广泛。
语法如下
CASE  WHEN 条件一
      THEN   结果一
      WHEN  条件二
      THEN   结果二
      ELSE    结果三
END

CASE最常用的功能：
１　将判断放在查询子句中合并查询。
２　进行行列转换
３　根据条件的不同返回不同的结果，以完成复杂逻辑判断

谢谢分享

谢谢楼主，学习了！

学习下学习下

结帖了

总结
使用CASE 完成查询所需要考虑的事情
1 是否访问相同的表。如果访问的表不同就不用考虑了
2 是否使用相同的列。 如果是基本肯定可以合并了
  如果不同的条件有组合 就需要注意业务逻辑。
3 如果使用的列不同。那么不同UNION 查询的条件是否有交集。

  如果没有 基本上也可以合并。不过需要注意合并后的性能问题。


CASE 无论在连接事务处理系统或联机分析系统中都得到了广泛的应用。
在某些 情况下，通过使用CASE替代UNION 可以起到事半功倍的效果。

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       ');

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

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

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

格式化报表

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