Oracle SQL 为每种帐户类型获得最低余额

Question

我试图列出客户的姓氏和名字以及帐户类型/描述，但仅列出每种帐户类型余额最低的客户。这是我到目前为止所拥有的：

Select Surname || ', ' || First_name AS "Name", account_description
from WGB_CUSTOMER join WGB_ACCOUNT using (Customer_number) join              
WGB_ACCOUNT_TYPE using (Account_type)
where salary <ANY
(select MIN(Balance), account_type
 from wgb_account
Group by account_type);

这是一个错误，并且说了太多值。代码的最后一部分（从选择MIN（余额）开始）本身仅显示余额最低的帐户类型。

Answer 1

使用rank() over (partition by

Select 
    Name, 
    account_description
from (
    select Surname || ', ' || First_name AS Name
    account_description
    rank() over (partition by Account_type order by Balance) rnk
    from WGB_CUSTOMER
) x
where rnk = 1;

使用join

Select 
    t.Surname || ', ' || t.First_name AS Name, 
    t.account_description
from wgb_account t  
join (
    select MIN(Balance) min_bal, account_type
    from wgb_account
    Group by account_type
) x
on x.min_bal = t.Balance
and x.account_type = t.account_type