乐趣区

PLSQL-练习题6-银行日常业务

jiezi

看完这章你会学习到以下内容：

  1. 解题的思路
  2. 解题的方法


问题 1：

解题思路：
1.YTD 数据按照产品名分类聚合求和（Group by + Sum 函数)
2. 产品名称出自 TblProduct 表，月份出自 TblPayments，两表关联。
3. 关联后用 With…AS 子查询套入查询
4. 列数比行数多，通常都是行列转换的结果

创建两张表格
1.tblpayment 表格创建语句

--- tblpayment 创建语句：CREATE TABLE TBLPAYMENTS
   (SID VARCHAR2(10), 
  PAYMENTTIME DATE, 
  PRODUCTID VARCHAR2(10), 
  AMOUNT NUMBER(10,0), 
  PAYMENTSTATUS VARCHAR2(10), 
  SALES VARCHAR2(10), 
  REGION VARCHAR2(10), 
  DELAYWEEKS VARCHAR2(20)
   );
  
insert into TBLPAYMENTS (SID, PAYMENTTIME, PRODUCTID, AMOUNT, PAYMENTSTATUS, SALES, REGION, DELAYWEEKS)
values ('S-1', to_date('02-01-2019 12:20:30', 'dd-mm-yyyy hh24:mi:ss'), 'P-1', 30, 'Paid', '张三', '华南', '每日按要求更新');
insert into TBLPAYMENTS (SID, PAYMENTTIME, PRODUCTID, AMOUNT, PAYMENTSTATUS, SALES, REGION, DELAYWEEKS)
values ('S-2', to_date('02-02-2019 12:20:30', 'dd-mm-yyyy hh24:mi:ss'), 'P-2', 60, 'Waiting', '张三', '华南', '每日按要求更新');
insert into TBLPAYMENTS (SID, PAYMENTTIME, PRODUCTID, AMOUNT, PAYMENTSTATUS, SALES, REGION, DELAYWEEKS)
values ('S-3', to_date('02-03-2019 22:20:30', 'dd-mm-yyyy hh24:mi:ss'), 'P-3', 90, 'Paid', '李四', '华东', '每日按要求更新');
insert into TBLPAYMENTS (SID, PAYMENTTIME, PRODUCTID, AMOUNT, PAYMENTSTATUS, SALES, REGION, DELAYWEEKS)
values ('S-4', to_date('04-03-2019 15:20:30', 'dd-mm-yyyy hh24:mi:ss'), 'P-1', 100, 'Waiting', '张三', '华南', '每日按要求更新');
insert into TBLPAYMENTS (SID, PAYMENTTIME, PRODUCTID, AMOUNT, PAYMENTSTATUS, SALES, REGION, DELAYWEEKS)
values ('S-5', to_date('02-04-2019 12:20:30', 'dd-mm-yyyy hh24:mi:ss'), 'P-2', 200, 'Waiting', '李四', '华南', '每日按要求更新');
insert into TBLPAYMENTS (SID, PAYMENTTIME, PRODUCTID, AMOUNT, PAYMENTSTATUS, SALES, REGION, DELAYWEEKS)
values ('S-6', to_date('22-05-2019 17:20:30', 'dd-mm-yyyy hh24:mi:ss'), 'P-3', 3000, 'Paid', '张三', '华东', '每日按要求更新');

2.tblproduct 表格创建语句

---- tblproduct 创建语句：CREATE TABLE TBLPRODUCT 
   (SID VARCHAR2(10),
  PRODUCTNAME VARCHAR2(20), 
  UNITPRICE NUMBER(20,0), 
  STATUS VARCHAR2(10)
   );

insert into TBLPRODUCT (SID, PRODUCTNAME, UNITPRICE, STATUS)
values ('P-1', '商品 A', 10, 'Active');
insert into TBLPRODUCT (SID, PRODUCTNAME, UNITPRICE, STATUS)
values ('P-2', '商品 B', 20, 'Active');
insert into TBLPRODUCT (SID, PRODUCTNAME, UNITPRICE, STATUS)
values ('P-3', '商品 C', 30, 'Inactive');

于是, 第一题的解题答案：


WITH T AS 
(SELECT NVL(TO_CHAR(TPAY.PAYMENTTIME, 'MM'),'TOTAL') AS 月份,
       TPRO.PRODUCTNAME,
       SUM(TPAY.AMOUNT) AS 金额
  FROM TBLPAYMENTS TPAY
  JOIN TBLPRODUCT TPRO
    ON TPAY.PRODUCTID = TPRO.SID
 WHERE TO_CHAR(TPAY.PAYMENTTIME,'YYYY') = '2019'
 GROUP BY ROLLUP(TO_CHAR(TPAY.PAYMENTTIME, 'MM')), TPRO.PRODUCTNAME

)


--- 数字和字符合并显示，要加双引号

SELECT * FROM T PIVOT(MAX(金额) FOR 月份 IN ('TOTAL' AS YTD,
                                '01' AS "1 月",'02' AS "2 月",'03' AS "3 月",
                                                                '04' AS "4 月",'05' AS "5 月",'06' AS "6 月",
                                                                '07' AS "7 月",'08' AS "8 月",'09' AS "9 月",
                                                                '10' AS "10 月",'11' AS "11 月",'12' AS "12 月"));

结果如下图所示：
注意：当有数字，又有字符串，记得用 ”” 括起来。

第二题：

解题思路：
1. 按区域按月份排名，既要分组又要排序 — 排序分析函数。
2. 按照两个字段条件排序，Partition by 两个字段。
3. 按照销售金额的大小排序，聚合函数 + group by + order by
4. 没有排序字段出现，创建新字段，全表嵌套子查询，倒叙排序后等于 1。

第一步至少要得出以下表格：

 SELECT REGION AS 区域名称,
           TO_CHAR(PAYMENTTIME, 'YYYY-MM') AS 年月,
           SALES AS 销售名称,
           SUM(AMOUNT) AS 销售额
    FROM tblPayments
    GROUP BY REGION,
             TO_CHAR(PAYMENTTIME, 'YYYY-MM'),
             SALES

第二步套用子查询和分析函数，取排名第一

SELECT 区域名称, 年月, 销售名称 
FROM(
  WITH T AS(
    SELECT REGION AS 区域名称,
           TO_CHAR(PAYMENTTIME, 'YYYY-MM') AS 年月,
           SALES AS 销售名称,
           SUM(AMOUNT) AS 销售额
    FROM tblPayments
    GROUP BY REGION,
             TO_CHAR(PAYMENTTIME, 'YYYY-MM'),
             SALES)
                 


   --- 根据两个字段分组排序，在实现在每个区域下的每个月的排名。SELECT T.*,
         ROW_NUMBER() OVER (PARTITION BY 区域名称, 年月 ORDER BY 销售额 DESC) AS 排名
  FROM T
 ORDER BY 年月)
WHERE 排名 =1;

第三题：

解题思路：
1.Case When 分条件判断；
2.UPDATE 表名 SET 字段 = 新值 WHERE 列名称 = 某值;

业务 2：
表格如下：剔除 第一单实付金额小于 100 元 的订单
再从剩下的订单钟找出某个用户第二次以来的复购订单。

解题思路：
1. 先找出第一笔订单是 100 元以下的，作为一张临时表。
与源表两张表用补集 Minus 计算出第二次复购的。
2.Case When 二分法，找出小于 100 元的则赋值为 0，否则为 1，最后再筛选出所有 1 的结果。
3. 两个条件是不是第一单，是不是小于 100 元。

方法 1(比较传统)：

SELECT * FROM T_PAY;

SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.CUS_ID ORDER BY PAY_TM) AS RN
  FROM T_PAY T
MINUS  
SELECT *
  FROM (SELECT T.*,
               ROW_NUMBER() OVER(PARTITION BY T.CUS_ID ORDER BY PAY_TM) AS RN
          FROM T_PAY T)
 WHERE RN = 1
   AND PAY_AMT < 100;

方法 2(比较讨巧)：

--- 思路：用 CASE WHEN 二分法判断，如果不满足的，则返回 0，如果满足的则返回 1
---  套用子查询，筛选符合题意的内容;
SELECT *
  FROM (SELECT T.*,
               CASE
                 WHEN RN = 1 AND PAY_AMT < 100 THEN 0
                 ELSE 1
               END FLAG
          FROM (SELECT T.*,
                       ROW_NUMBER() OVER(PARTITION BY T.CUS_ID ORDER BY PAY_TM) AS RN
                  FROM T_PAY T) T) T1
 WHERE T1.FLAG = 1;

方法 3(比较推荐)：

 SELECT * FROM (SELECT T.*,ROW_NUMBER()OVER(PARTITION BY T.CUS_ID ORDER BY T.PAY_AMT) AS RN
       FROM T_PAY T
 )A WHERE RN!=1 OR A.PAY_AMT > 100;
退出移动版