MySQL - 分组和总计，但返回每组中的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28158083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Group and total, but return all rows in each group
提问by MJ Bonanno
I'm trying to write a query that finds each time the same person occurs in my table between a specific date range. It then groups this person and totals their spending for a specific range. If their spending habits are greater than X amount, then return each and every row for this person between date range specified. Not just the grouped total amount. This is what I have so far:
我正在尝试编写一个查询，该查询在特定日期范围内每次在我的表中出现同一个人时进行查找。然后将这个人分组并汇总他们在特定范围内的支出。如果他们的消费习惯大于 X 金额，则返回此人在指定日期范围内的每一行。不仅仅是分组的总金额。这是我到目前为止：
SELECT member_id,
SUM(amount) AS total
FROM `sold_items`
GROUP BY member_id
HAVING total > 50
This is retrieving the correct total and returning members spending over $50, but not each and every row. Just the total for each member and their grand total. I'm currently querying the whole table, I didn't add in the date ranges yet.
这是检索正确的总数和花费超过 50 美元的返回成员，但不是每一行。只是每个成员的总数和他们的总数。我目前正在查询整个表，我还没有添加日期范围。
回答by Barmar
JOINthis subquery with the original table:
JOIN这个带有原始表的子查询：
SELECT si1.*
FROM sold_items AS si1
JOIN (SELECT member_id
FROM sold_items
GROUP BY member_id
HAVING SUM(amount) > 50) AS si2
ON si1.member_id = si2.member_id
The general rule is that the subquery groups by the same column(s) that it's selecting, and then you join that with the original query using the same columns.
一般规则是子查询按它选择的相同列分组，然后使用相同的列将其与原始查询连接。
回答by kishkash
SELECT member_id, amount
FROM sold_items si
INNER JOIN (SELECT member_id,
SUM(amount) AS total
FROM `sold_items`
GROUP BY member_id
HAVING total > 50) spenders USING (member_id)
The query you have already built can be used as a temporary table to join with. if member_id is not an index on the table, this will become slow with scale. The word spenders is a table alias, you can use any valid alias in its stead.
您已经构建的查询可以用作要连接的临时表。如果 member_id 不是表上的索引，这将随着规模变慢。单词spenders 是一个表别名，您可以使用任何有效的别名来代替它。
回答by kishkash
I think that this might help:
我认为这可能会有所帮助：
SELECT
member_id,
SUM(amount) AS amount_value,
'TOTAL' as amount_type
FROM
`sold_items`
GROUP BY
member_id
HAVING
SUM(amount) > 50
UNION ALL
SELECT
member_id,
amount AS amount_value,
'DETAILED' as amount_type
FROM
`sold_items`
INNER JOIN
(
SELECT
A.member_id,
SUM(amount) AS total
FROM
`sold_items` A
GROUP BY
member_id
HAVING
total <= 50
) AS A
ON `sold_items`.member_id = A.member_id
Results of the above query should be like the following:
上述查询的结果应如下所示：
member_id amount_value amount_type
==========================================
1 55 TOTAL
2 10 DETAILED
2 15 DETAILED
2 10 DETAILED
so the column amount_typewould distinguish the two specific member groups
因此该列amount_type将区分两个特定的成员组
回答by georgexsh
You could do subquery with EXISTSas an alternative:
您可以使用以下方法进行子查询EXISTS：
select *
from sold_items t1
where exists (
select * from sold_items t2
where t1.member_id=t2.member_id
group by member_id
having sum(amount)>50
)
ref: http://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html
参考：http: //dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html
回答by Andrew Stretton
There are a few syntaxes that will get the result you are looking, here is one using an inner join to ensure that all rows returned have a member_id in the list returned by the group by and that the total is repeated for each a certain member has:
有一些语法可以得到您正在查看的结果，这里是一种使用内部联接来确保返回的所有行在 group by 返回的列表中都有一个 member_id 并且对于每个特定成员重复总数：
SELECT si.*, gb.total from sold_items as si, (SELECT member_id as mid,
SUM(amount) AS total
FROM `sold_items`
GROUP BY member_id
HAVING total > 50) as gb where gb.mid=si.member_id;
