ROWNUM函数实现排名环比(二)

最新推荐文章于 2022-05-19 17:12:04 发布

dushihuiand1

最新推荐文章于 2022-05-19 17:12:04 发布

阅读量484

点赞数 1

分类专栏： sql Oracle 文章标签： sql 数据库经验需求企业

本文链接：https://blog.csdn.net/dushihuiand1/article/details/72988163

版权

sql 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

Oracle

7 篇文章 0 订阅

订阅专栏

在上一篇文章中，我们建立了两张用于测试的表。如下：

SQL> select * from m_store;

   ID NAME
---------- ----------
      1001 shop1
      1002 shop2
      1003 shop3
      1004 shop4
      1005 shop5

SQL> select * from retail_s;

   ID BILLDA   STORE_ID   AMOUNT
---------- ------ ---------- ----------
     10001 201705   1001      50000
     10002 201705   1002      60000
     10003 201705   1003      40000
     10004 201705   1004      70000
     10005 201705   1005      10000
     10006 201704   1001   100000
     10007 201704   1002      80000
     10008 201704   1003      50000
     10009 201704   1004      70000
     10010 201704   1005      30000

第一步，我们先将当期数据检索出来，并进行排序

SQL> SELECT S.NAME,R.AMOUNT FROM RETAIL_S R JOIN M_STORE S ON R.STORE_ID=S.ID WHERE R.BILLDATE=201705 ORDER BY R.AMOUNT DESC;

NAME           AMOUNT
---------- ----------
shop4            70000
shop2            60000
shop1            50000
shop3            40000
shop5            10000

第二步，使用ROWNUM伪列进行排名，这里必须要嵌套一层子查询

SQL> SELECT ROWNUM AS TOP,A.* FROM (
     SELECT S.NAME,R.AMOUNT FROM RETAIL_S R JOIN M_STORE S ON R.STORE_ID=S.ID 
     WHERE R.BILLDATE=201705 ORDER BY R.AMOUNT DESC) A;

       TOP NAME        AMOUNT
---------- ---------- ----------
         1 shop4       70000
         2 shop2       60000
         3 shop1       50000
         4 shop3       40000
         5 shop5       10000

第三步，把上期数据也这样检索出来

SQL> SELECT ROWNUM AS TOP,A.* FROM (
     SELECT S.NAME,R.AMOUNT FROM RETAIL_S R JOIN M_STORE S ON R.STORE_ID=S.ID 
     WHERE R.BILLDATE=201704 ORDER BY R.AMOUNT DESC) A;

       TOP NAME       AMOUNT
---------- ---------- ----------
         1 shop1      100000
         2 shop2       80000
         3 shop4       70000
         4 shop3       50000
         5 shop5       30000

第四步，使用连接查询将两个表连起来

SELECT X.*,Y.TOP AS TOP_OLD,Y.AMOUNT AS AMOUNT_OLD FROM
(SELECT ROWNUM AS TOP,A.* FROM
(SELECT S.NAME,R.AMOUNT FROM RETAIL_S R JOIN M_STORE S ON R.STORE_ID=S.ID WHERE R.BILLDATE=201705 ORDER BY R.AMOUNT DESC) A) X
LEFT JOIN
(SELECT ROWNUM AS TOP,A.* FROM
(SELECT S.NAME,R.AMOUNT FROM RETAIL_S R JOIN M_STORE S ON R.STORE_ID=S.ID WHERE R.BILLDATE=201704 ORDER BY R.AMOUNT DESC) A) Y
ON X.NAME=Y.NAME ORDER BY 1

       TOP NAME       AMOUNT        TOP_OLD AMOUNT_OLD
---------- ---------- ---------- ---------- ----------
         1 shop4       70000             3         70000
         2 shop2       60000             2         80000
         3 shop1       50000             1        100000
         4 shop3       40000             4         50000
         5 shop5       10000             5         30000

这样，我们就得到了一个销售排名的环比报表

接下来，我们用PL/SQL再实现一次

CREATE OR REPLACE PROCEDURE "SCOTT"."GET_TOP" (V_DATE NUMBER)
IS 
CURSOR C1
IS
SELECT X.*,Y.TOP AS TOP_OLD,Y.AMOUNT AS AMOUNT_OLD FROM 
(SELECT ROWNUM AS TOP,A.* FROM 
(SELECT S.NAME,R.AMOUNT FROM RETAIL_S R JOIN M_STORE S ON R.STORE_ID=S.ID WHERE R.BILLDATE=V_DATE ORDER BY R.AMOUNT DESC) A) X
LEFT JOIN 
(SELECT ROWNUM AS TOP,A.* FROM 
(SELECT S.NAME,R.AMOUNT FROM RETAIL_S R JOIN M_STORE S ON R.STORE_ID=S.ID WHERE R.BILLDATE=V_DATE-1 ORDER BY R.AMOUNT DESC) A) Y
ON X.NAME=Y.NAME ORDER BY 1;
BEGIN
FOR I IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(I.TOP||'   '||I.NAME||'   '||I.AMOUNT||'    '||I.TOP_OLD||'    '||I.AMOUNT_OLD);
END LOOP;
END GET_TOP;

测试一下

SQL> exec get_top(201705);

1   shop4   70000    3      70000
2   shop2   60000    2      80000
3   shop1   50000    1      100000
4   shop3   40000    4      50000
5   shop5   10000    5      30000

PL/SQL procedure successfully completed.