MySQL GROUP BY两列 [英] MySQL GROUP BY two columns
问题描述
我试图按多个列分组 - 每个表上有一个。
这是一个场景，其中我想通过添加他们当前的投资组合和现金，找到每个客户的最高投资组合价值，一个客户可能有多个投资组合，所以我需要为每个客户的顶级投资组合。
目前，使用下面的代码我得到相同的客户端多个
SELECT clients.id，clients.name，投资组合.id，SUM（portfolios.portfolio + portfolios.cash）AS total
从客户端，组合
WHERE clients.id = portfolios.client_id
GROUP BY portfolios.id，clients.id
ORDER BY total DESC
LIMIT 30
首先，让我们做一些测试数据：
创建表客户端（client_id integer not null主键auto_increment，
name varchar（64））;
创建表组合（portfolio_id integer not null主键auto_increment，
client_id整数引用client.id，
现金十进制（10,2），
股份十进制（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，则很容易：
>
使用（client_id）从客户端连接组合中选择client_id，name，max（cash + stocks）
group by client_id
+ ----------- + ---------- + -------------------- +
| client_id |名称| max（现金+股票）|
+ ----------- + ---------- + -------------------- +
| 1 | John Doe | 33.33 |
| 2 | Jane Doe | 90.90 |
+ ----------- + ---------- + -------------------- +
由于您需要投资组合ID，因此事情变得更复杂。让我们的步骤。首先，我们将写一个子查询，返回每个客户端的最大组合值：
select client_id，max ）as maxtotal
from portfolio
group by client_id
+ ----------- + ---------- +
| client_id | maxtotal |
+ ----------- + ---------- +
| 1 | 33.33 |
| 2 | 90.90 |
+ ----------- + ---------- +
然后，我们将查询投资组合表，但使用到以前的子查询的连接，以便只保留那些投资组合的总值是客户端的最大值：
>
从投资组合
中选择投资组合id，现金+股票（从选项组合$ b中选择client_id，max（现金+股票）作为maxtotal
$ b group by client_id）as maxima
使用（client_id）
其中cash + stocks = maxtotal
+ -------------- + --------------- +
| portfolio_id |现金+股票|
+ -------------- + --------------- +
| 5 | 33.33 |
| 6 | 33.33 |
| 8 | 90.90 |
+ -------------- + --------------- +
$ b pre
$ b b
select client_id，name，portfolio_id，cash + stocks
从客户端
使用（client_id）加入组合
join（select client_id，max现金+股票）作为maxtotal
从投资组合
group by client_id）as maxima
使用（client_id）
其中cash + stocks = maxtotal
+ ---------- + ---------- + -------------- + ------------- - +
| client_id |名称| portfolio_id |现金+股票|
+ ----------- + ---------- + -------------- + ------- -------- +
| 1 | John Doe | 5 | 33.33 |
| 1 | John Doe | 6 | 33.33 |
| 2 | Jane Doe | 8 | 90.90 |
+ ----------- + ---------- + -------------- + ------- -------- +
注意，这会为John Doe返回两行，两个具有完全相同的总价值的投资组合。要避免这种情况并选择任意顶级投资组合，请在GROUP BY子句上标记：
选择client_id，name，portfolio_id，cash +股票
从客户
加入投资组合使用（client_id）
加入（选择client_id，最大（现金+股票）作为maxtotal
从投资组合
组由client_id）作为最大
使用（client_id）
其中cash + stocks = maxtotal
group by client_id，cash + stocks
+ ----------- + ---------- + -------------- + --------------- +
| client_id |名称| portfolio_id |现金+股票|
+ ----------- + ---------- + -------------- + ------- -------- +
| 1 | John Doe | 5 | 33.33 |
| 2 | Jane Doe | 8 | 90.90 |
+ ----------- + ---------- + -------------- + ------- -------- +
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).
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
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:
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:
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:
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 |
+-----------+----------+--------------+---------------+
这篇关于MySQL GROUP BY两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
