如何编写sql查询查找人的财富？

Question

我在编写一个查询时遇到了困难，该查询返回所有财富不足最富有者一半的人的姓名。我们把一个人的财富定义为他/她所有账户上的总财富。

这3张表格是：

Persons

id | name | address | age | eyeColor | gender

BankAccounts

id | balance

AccountOf

id | person_id → Persons | account_id → BankAccounts

有人能帮帮我吗？

SELECT Persons.name
FROM Persons P1
LEFT JOIN AccountOf A1 ON A1.person_id = P1.id
LEFT JOIN BankAccounts B ON B.id = A1.account_id
GROUP BY name
HAVING SUM(B.balance) < MAX((SELECT SUM(B.balance) as b
  FROM AccountOf A1
  LEFT JOIN BankAccounts B ON B.id = A1.account_id
  GROUP BY A1.person_id ORDER BY b DESC  LIMIT 1)
) * 0.5;

Answer 1

您没有提供任何示例数据或DBFiddle，因此这当然是未经测试的，但是您可以使用CTE*来构造一个查询：

with p as (
    select p.name, Sum(b.balance) balance
    from persons p
    join accountOf a on a.person_id = p.Id
    join BankAccounts b on b.Id = a.account_id
    group by p.name
)
select *
from p
where p.balance < (select Max(balance) from p) * 0.5;

(*需要MySql 8+)