sql - ORACLE SQL 我遇到了一个选择语句的问题，该语句应该显示每个分支的最大余额

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;

结果

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

标签： sqloraclegreatest-n-per-group

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;