MySQL GROUP BY 两列

MySQL GROUP BY two columns

提问by ChrisS

I'm trying to group by multiple columns here - one on each table.
It's a scenario where I want to find the top portfolio value for each client by adding their current portfolio and cash together but a client may have more than one portfolio, so I need the top portfolio for each client.

我正在尝试按多列进行分组 - 每张桌子上有一个。
在这种情况下，我想通过将他们当前的投资组合和现金加在一起来为每个客户找到最高的投资组合价值，但一个客户可能有多个投资组合，所以我需要每个客户的最高投资组合。

At the moment, with the code below I'm getting the same clients multiple times for each of their top portfolios (it's not grouping by client id).

目前，使用下面的代码，我为每个顶级投资组合多次获得相同的客户（不是按客户 ID 分组）。

SELECT clients.id, clients.name, portfolios.id, SUM ( portfolios.portfolio +  portfolios.cash ) AS total
FROM clients, portfolios
WHERE clients.id = portfolios.client_id
GROUP BY portfolios.id, clients.id
ORDER BY total DESC
LIMIT 30 

回答by Vebjorn Ljosa

First, let's make some test data:

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

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

If you didn't need the portfolio ID, it would be easy:

如果您不需要投资组合 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 | 
+-----------+----------+--------------------+

Since you need the portfolio ID, things get more complicated. Let's do it in steps. First, we'll write a subquery that returns the maximal portfolio value for each client:

由于您需要投资组合 ID，因此事情变得更加复杂。让我们分步进行。首先，我们将编写一个子查询，返回每个客户的最大投资组合值：

select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id

+-----------+----------+
| client_id | maxtotal |
+-----------+----------+
|         1 |    33.33 | 
|         2 |    90.90 | 
+-----------+----------+

Then we'll query the portfolio table, but use a join to the previous subquery in order to keep only those portfolios the total value of which is the maximal for the client:

然后我们将查询投资组合表，但使用与前一个子查询的连接，以便仅保留那些总价值为客户端最大值的投资组合：

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

Finally, we can join to the client table (as you did) in order to include the name of each client:

最后，我们可以加入客户端表（如您所做的那样）以包含每个客户端的名称：

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

Note that this returns two rows for John Doe because he has two portfolios with the exact same total value. To avoid this and pick an arbitrary top portfolio, tag on a GROUP BY clause:

请注意，这会为 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 | 
+-----------+----------+--------------+---------------+

回答by Jose' Vargas

Using Concat on the group by will work

在 group by 上使用 Concat 会起作用

SELECT clients.id, clients.name, portfolios.id, SUM ( portfolios.portfolio + portfolios.cash ) AS total
FROM clients, portfolios
WHERE clients.id = portfolios.client_id
GROUP BY CONCAT(portfolios.id, "-", clients.id)
ORDER BY total DESC
LIMIT 30