SUM WinSQL之后的第五个最大值-项目数量最高的分支

Question

下面的链接是一个很好的例子，但这段代码不起作用，我无法使用max（）和sum（）

Oracle信息：

Oracle Database 11g Enterprise Edition 11.2.0.2.0-64位生产版
PL/SQL版本11.2.0.2.0-生产
CORE 11.2.0.2.0生产
TNS for Linux：版本11.2.0.2.0-生产
NLSRTL版本11.2.0.2.0-生产

我需要从2个表中检索特定项目的延期订单最高的分支。对于每个项目，都有不同的客户，因此每个分支机构每个项目都有单独的延期订单数量。我需要添加项目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

也尝试过（为了示例，只使用1个表）：

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

我错过了什么？

THanks

Answer 1

在Oracle12c+中，您可以执行：

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