mysql - SQL 聚合子查询

Question

我想查询那些财富少于最富有的人一半财富的人。所以我想出了以下查询：

select P.name
from
    (select sum(B.balance) / 2 as Balance
    from Person P1, BankAccount B, AccountOf A
    where P.id = A.person_id and A.account_id = B.id
    group by P1.id) as X, Persons P, BankAccount B, AccountOf A
    
where  
group by P.id
having sum(B.balance) < max(X.Balance) 

有人可以向我解释我做错了什么，在我看来，正常查询中出现了问题，因为单独完成的子查询给出了正确的数量。

标签： mysqlsqlfunction

Answer 1

“询问那些财富少于最富有的人一半财富的人”

select id,Name
from (
        select
            P.id,P.Name
            sum(B.balance) as Balance,
            max(sum(B.balance)) over() richestbalance
        from
            Person P
            join BankAccount B on P.id = A.person_id
            join AccountOf A on A.account_id = B.id
        group by P.id,P.Name
    ) t
where Balance < richestbalance / 2

然后使用您的查询：

select P.name
from
    (select sum(B.balance) as Balance
    from Person P1, BankAccount B, AccountOf A
    where P.id = A.person_id and A.account_id = B.id
    group by P1.id) as X, Persons P, BankAccount B, AccountOf A
    
where  ...
group by P.id
having sum(B.balance) < max(X.Balance)/2