MySQL GROUP BY两列

MySQL GROUP BY两列

首先，让我们做一些测试数据：

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 maxtotalfrom portfoliogroup 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 + stocksfrom clientjoin portfolio using (client_id)join (select client_id, max(cash + stocks) as maxtotal      from portfolio       group by client_id) as maximausing (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 | +-----------+----------+--------------+---------------+

请注意，这将返回John Doe的两行，因为他有两个总价值完全相同的投资组合。为了避免这种情况并选择任意的顶级投资组合，请在GROUP BY子句上进行标记：

select client_id, name, portfolio_id, cash + stocksfrom clientjoin portfolio using (client_id)join (select client_id, max(cash + stocks) as maxtotal      from portfolio       group by client_id) as maximausing (client_id)where cash + stocks = maxtotalgroup by client_id, cash + stocks+-----------+----------+--------------+---------------+| client_id | name     | portfolio_id | cash + stocks |+-----------+----------+--------------+---------------+|         1 | John Doe | 5 |         33.33 | |         2 | Jane Doe | 8 |         90.90 | +-----------+----------+--------------+---------------+