ORACLE SQL我对一个select语句有问题，该语句应该显示每个分支的最大余额

Question

大家好，感谢大家帮助我。

我有这个问题：

SELECT  c.accNum.bID.bID AS "Branch ID",
--c.accNum.accNumber AS "Account Number",
--c.accHolder.PrintFullName() AS "Customer Name",
MAX(c.accNum.balance) AS "Balance"
FROM tabCustomersAccounts c
GROUP BY c.accNum.bID.bID--, c.accNum.accNumber, c.accHolder.PrintFullName(), c.accNum.balance
ORDER BY c.accNum.bID.bID;

结果

当我将代码更改为：

SELECT  c.accNum.bID.bID AS "Branch ID",
c.accNum.accNumber AS "Account Number",
c.accHolder.PrintFullName() AS "Customer Name",
MAX(c.accNum.balance) AS "Balance"
FROM tabCustomersAccounts c
GROUP BY c.accNum.bID.bID, c.accNum.accNumber, c.accHolder.PrintFullName(), c.accNum.balance
ORDER BY c.accNum.bID.bID;

结果

从这个结果中，你可以看到我他们错了，因为我只想显示每个分支的最大余额。

Answer 1

使用row_number()。您的实际列名有点不清楚，但大致如下：

SELECT ca.*
FROM (SELECT ca.*,
ROW_NUMBER() OVER (PARTITION BY ca.bid ORDER BY c.balance DESC) as seqnum
FROM tabCustomersAccounts ca
) ca
WHERE seqnum = 1;