MySQL按两列分组
MySQL按两列分组
提问于 2010-02-02 18:47:46
在这里，我尝试按多个列进行分组-每个表上一个列。
这是一种方案，我希望通过将每个客户的当前投资组合和现金相加来找到每个客户的最高投资组合价值，但一个客户可能有多个投资组合，因此我需要每个客户的最高投资组合。
目前，使用下面的代码，我会多次为每个顶级投资组合获得相同的客户(不是按客户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 回答 2
Stack Overflow用户
回答已采纳
发布于 2010-02-02 19:50:53
首先，让我们做一些测试数据：
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);如果您不需要portfolio 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 |
+-----------+----------+然后，我们将查询portfolio表，但使用前一个子查询的连接，以便只保留客户端的合计值最大的那些投资组合：
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 |
+-----------+----------+--------------+---------------+Stack Overflow用户
发布于 2015-05-23 04:04:33
在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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2183373
复制相关文章
相似问题