时间戳

首页 > 解决方案 > 帐户中超过 100 万的人

mysql - 帐户中超过 100 万的人

问题描述

假设我们有 3 个表：

CREATE TABLE Persons (
  id int(10),
  name varchar(100),
  address varchar(100),
  age int(3),
  eyeColor varchar(100),
  gender varchar(100)
);
INSERT INTO Persons VALUES
(11,'Woody allen','Amsterdam',35,'green','male'),
(12,'Charlton Heston','Rome',68,'green','male'),
(13,'Katharine Hepburn','Rome',87,'brown','female'),
(14,'Rachel McAdams','Leipzig',70,'blue','female')
;

CREATE TABLE BankAccounts (
  id int(10),
  balance decimal(10,2)
);
INSERT INTO BankAccounts VALUES
(11,1600931.91),
(12,1437877.7),
(13,267971.26),
(14,949724.05)
;

CREATE TABLE AccountOf (
  id int(10),
  person_id int(10),  -- foreign key to Persons.id
  account_id int(10)  -- foreign key to BankAccounts.id
);
INSERT INTO AccountOf VALUES
(18,11,13),
(17,12,12),
(20,12,14),
(16,14,11),
(19,14,13)
;

我必须编写一个查询，返回所有银行账户中超过 100 万欧元的人。

我想到了以下几点：

SELECT name 
FROM Persons
JOIN BankAccounts ON BankAccounts.id=Persons.id
WHERE BankAccounts.Balance > 1000000

关于如何改进此代码的任何建议？预期的结果应该是

name
-------------------
Rachel McAdams
Charlton Heston

标签： mysqldatabase

解决方案

SELECT
    p.id,
    p.name,
    SUM(ba.balance)
FROM Persons AS p
    INNER JOIN AccountOf AS aof
        ON aof.person_id = p.id
    INNER JOIN BankAccounts AS ba
        ON ba.id = aof.account_id
GROUP BY p.id, p.name
HAVING SUM(ba.balance) > 1000000
;

你可以在这个DBFiddle中看到结果

推荐阅读