Oracle子查询问题，查找上一年与当年销售产品数量之间的差异

A.K. Singh 发表于 Dev

阿克·辛格

我是Oracle数据库的新手，但是在学习oracle时我对Teradata和MSSQL有很好的经验，下面两张表面临一个概念上的混乱：

  Create table PRODUCTS
(
PRODUCT_ID INTEGER NOT NULL PRIMARY KEY,
PRODUCT_NAME VARCHAR2(30)
);
CREATE TABLE SALES
(
SALE_ID INTEGER NOT NULL PRIMARY KEY,
PRODUCT_ID INTEGER,
YEAR INTEGER,
QUANTITY INTEGER,
PRICE INTEGER,
FOREIGN KEY(PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
);

该表的样本值是：

    INSERT INTO PRODUCTS VALUES ( 100, 'A');
INSERT INTO PRODUCTS VALUES ( 200, 'B');
INSERT INTO PRODUCTS VALUES ( 300, 'C');
INSERT INTO PRODUCTS VALUES ( 400, 'D');
INSERT INTO SALES VALUES ( 1, 100, 2010, 25, 5000);
INSERT INTO SALES VALUES ( 2, 100, 2011, 16, 5000);
INSERT INTO SALES VALUES ( 3, 100, 2012, 8,  5000);
INSERT INTO SALES VALUES ( 4, 200, 2010, 10, 9000);
INSERT INTO SALES VALUES ( 5, 200, 2011, 15, 9000);
INSERT INTO SALES VALUES ( 6, 200, 2012, 20, 9000);
INSERT INTO SALES VALUES ( 7, 300, 2010, 20, 7000);
INSERT INTO SALES VALUES ( 8, 300, 2011, 18, 7000);
INSERT INTO SALES VALUES ( 9, 300, 2012, 20, 7000);

我的目标是从此表中查找以下内容：****编辑****我的预期结果是：对于产品，假设'A'数量差异=（当前年份数量-上一年数量）并且如果数量差异大于0，则说明它应该仅显示产品名称。在上述情况下：输出为/** We only need to compare the previous year quantity sold with current year quantity sold if the current year quantity is greater than previous year we need to display that product name**/

Product_Name
--------------------
B
C

1-产品数量与上一年数量之间的差异？

为此，我正在使用以下查询：

SELECT
 PRODUCT_NAME
 from
 (
    SELECT
           P.PRODUCT_NAME,
           P.PRODUCT_ID,
           S.PRODUCT_ID,
          ((S.QUANTITY)-(LEAD(S.QUANTITY,1,0) OVER (PARTITION BY P.PRODUCT_ID ORDER BY S.YEAR DESC))) AS QUANTITY_DIFFERENCE
    FROM
         PRODUCTS P,
         SALES S
    WHERE 
         QUANTITY_DIFFERENCE>=0
    )A 
     GROUP BY PRODUCT_NAME
    ;

它给出了以下错误，我试图修复它，但无法实现更正。

ORA-00904: "QUANTITY_DIFFERENCE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

编辑我刚刚通过民间的建议纠正了先前的错误，现在修改后的查询是：

   SELECT
     PRODUCT_NAME,
     QUANTITY_DIFFERENCE
     from
     (
        SELECT PRODUCT_NAME,
               QUANTITY_DIFFERENCE FROM (
          SELECT
               P.PRODUCT_NAME,
               P.PRODUCT_ID,
               S.PRODUCT_ID,
              ((S.QUANTITY)-(LEAD(S.QUANTITY,1,0) OVER (PARTITION BY P.PRODUCT_ID ORDER BY S.YEAR DESC))) AS QUANTITY_DIFFERENCE
        FROM
             PRODUCTS P,
             SALES S
        ) WHERE 
             QUANTITY_DIFFERENCE>=0
        )A 
         GROUP BY PRODUCT_NAME,
         HAVING QUANTITY_DIFFERENCE>=0
        ;

但是现在我收到以下错误：

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:

Please also guide me for the correct logical approach for achieving the objective of "Finding the difference between current year quantity and previous years' quantity. For example suppose the 2011 quantity was 10 and 2012 quantity was 8 then it should -2 but currently i need to show only quantities difference which is having difference more than zero.

你们能否指导我如何解决此问题，还有其他方法可以编写相同的查询吗？

斯坦

不需要group by。但是您必须指定当前年份。否则，您的查询将开始考虑多年的数据集，这听起来并不像您想要的那样。

select p.product_name
  from (select s.*,
               s.quantity - lag(s.quantity) over (partition by product_id order by year) as qty_diff
          from sales s) s
  join products p
    on p.product_id = s.product_id
 where s.year = 2012 -- set your current year here
   and s.qty_diff > 0

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。