首先,让我们创建一些测试数据:
create table client (client_id integer not null primary key auto_increment,
name varchar(64));
create table portfolio (portfolio_id integer not null primary key auto_increment,
client_id integer references client.id,
cash decimal(10,2),
stocks decimal(10,2));
insert into client (name) values ('John Doe'), ('Jane Doe');
insert into portfolio (client_id, cash, stocks) values (1, 11.11, 22.22),
(1, 10.11, 23.22),
(2, 30.30, 40.40),
(2, 40.40, 50.50);
如果您不需要投资组合ID,那么这将非常简单:
select client_id, name, max(cash + stocks)
from client join portfolio using (client_id)
group by client_id
+
| client_id | name | max(cash + stocks) |
+
| 1 | John Doe | 33.33 |
| 2 | Jane Doe | 90.90 |
+
由于您需要投资组合ID,事情会变得更加复杂。我们将分步骤进行。首先,我们将编写一个子查询,返回每个客户的最大投资组合价值:
select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id
+
| client_id | maxtotal |
+
| 1 | 33.33 |
| 2 | 90.90 |
+
然后我们将查询投资组合表,但使用连接到前一个子查询,以便仅保留客户总价值最大的投资组合:
select portfolio_id, cash + stocks from portfolio
join (select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal
+--------------+---------------+
| portfolio_id | cash + stocks |
+--------------+---------------+
| 5 | 33.33 |
| 6 | 33.33 |
| 8 | 90.90 |
+--------------+---------------+
最后,我们可以像你所做的那样加入客户表,以便包括每个客户的姓名:
select client_id, name, portfolio_id, cash + stocks
from client
join portfolio using (client_id)
join (select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal
+
| client_id | name | portfolio_id | cash + stocks |
+
| 1 | John Doe | 5 | 33.33 |
| 1 | John Doe | 6 | 33.33 |
| 2 | Jane Doe | 8 | 90.90 |
+
请注意,这里返回了约翰·杜的两行数据,因为他有两个投资组合具有完全相同的总价值。为避免这种情况并选择任意一个顶级投资组合,请添加一个GROUP BY子句:
select client_id, name, portfolio_id, cash + stocks
from client
join portfolio using (client_id)
join (select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal
group by client_id, cash + stocks
+
| client_id | name | portfolio_id | cash + stocks |
+
| 1 | John Doe | 5 | 33.33 |
| 2 | Jane Doe | 8 | 90.90 |
+