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 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 |

+-----------+----------+--------------+---------------+

请注意，这为John Doe返回两行，因为他有两个具有完全相同总值的投资组合。为了避免这种情况并选择任意的顶级投资组合，在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 |

+-----------+----------+--------------+---------------+