一项质量最高的FInd最大和WinSQL分支

Question

下面的链接是一个封闭的例子，但是这段代码不起作用，我也找不出-> using max() and sum()

甲骨文信息：

Oracle数据库11g企业版发布11.2.0.2.064位生产

PL/SQL版本11.2.0.2.0 -生产

核心11.2.0.2.0生产

TNS for Linux: Version 11.2.0.2.0 -生产

NLSRTL版本11.2.0.2.0 -生产

我需要从两个表中检索一个特定项目的最高退货订单。对于每个项目，都有不同的客户端，因此每个分支的每个项目都有不同的退货量。我需要把A项的所有数量加起来，看看哪个分店的退货数量最多。加起来，然后麦克斯..。

如果我这样做了：

SELECT BRANCH, ITEM, MAX(QTY) AS LEQTY
FROM
(

    SELECT BRANCH, ITEM, SUM(QTY) AS LEQTY
       FROM TABLE1
     GROUP BY BRANCH, ITEM
    UNION ALL
    SELECT BRANCH, ITEM, SUM(QTY) AS LEQTY
       FROM TABLE2
     GROUP BY BRANCH, ITEM
    )
    WHERE ITEM = 'XYZ'
    GROUP BY BRANCH, ITEM

我得到：

(QTY是每个分支机构的总和)

   BRANCH   ITEM    QTY
   ------   ----    ---
   BRANCH1  XYZ     3
   BRANCH2  XYZ     0
   BRANCH3  XYZ     4
   BRANCH4  XYZ     21

但我需要：

BRANCH   ITEM    QTY
------   ----    ---
BRANCH4  XYZ     21

也尝试过(为了示例，只使用一个表)：

SELECT BRANCH, ITEM, MAX(QTY)
FROM TABLE1
WHERE QTY = (SELECT SUM(QTY)
                FROM TABLE1
                WHERE ITEM = 'XYZ'
                )
  AND ITEM = 'XYZ'
GROUP BY BRANCH, ITEM

它给了我一条线，但错了。

最后，我想出了如下结论：

SELECT A.BRANCH, A.ITEM, MAX(A.QTY) AS ITEM
FROM TABLE1 A, (SELECT BRANCH, ITEM, SUM(QTY) AS LEQTY
                           FROM TABLE1
                           GROUP BY BRANCH, ITEM) B
      WHERE A.BRANCH = B.BRANCH
      AND MAX(B.QTY)
GROUP BY A.BRANCH, A.ITEM

这给了我一个ORA-00934

我遗漏了什么？

谢谢

Answer 1

在甲骨文12c+中，您可以：

SELECT BRANCH, ITEM, MAX(QTY) AS LEQTY
FROM ((SELECT BRANCH, ITEM, SUM(QTY) AS LEQTY
       FROM TABLE1
       GROUP BY BRANCH, ITEM
      ) UNION ALL
      (SELECT BRANCH, ITEM, SUM(QTY) AS LEQTY
       FROM TABLE2
       GROUP BY BRANCH, ITEM
      )
     ) bi
WHERE ITEM = 'XYZ'
GROUP BY BRANCH, ITEM
ORDER BY MAX(QTY) DESC
FETCH FIRST 1 ROW ONLY;

在早期版本中：

SELECT BRANCH, ITEM, LEQTY
FROM (SELECT BRANCH, ITEM, MAX(QTY) AS LEQTY, rownum as rn
      FROM ((SELECT BRANCH, ITEM, SUM(QTY) AS LEQTY
             FROM TABLE1
             GROUP BY BRANCH, ITEM
            ) UNION ALL
            (SELECT BRANCH, ITEM, SUM(QTY) AS LEQTY
             FROM TABLE2
             GROUP BY BRANCH, ITEM
            )
           ) bi
      WHERE ITEM = 'XYZ'
      GROUP BY BRANCH, ITEM
      ORDER BY MAX(QTY) DESC
     ) bi
WHERE rn = 1;

Answer 2

好吧，所以我能用这个

SELECT
    branch, item, BO_qty
FROM
    (SELECT
        branch, item, SUM(BO_qty) AS sum_bo
     FROM
        (SELECT
            branch, item, SUM(BO_qty) AS sum_bo
         FROM
            t1
         GROUP BY
            branch, item
        UNION ALL
         SELECT
            branch, item, SUM(BO_qty) AS sum_bo
         FROM 
            t2
         GROUP BY
            branch, item
        )
     GROUP BY
        branch,
        item
     ORDER BY
        BO_qty DESC
    )
WHERE
    ROWNUM=1

它回来了

BRANCH   ITEM    QTY
------   ----    ---
BRANCH4  XYZ     21