MySQL-分组和总计，但返回每个分组中的所有行 [英] MySQL - Group and total, but return all rows in each group

问题描述

我正在尝试编写一个查询，该查询查找在特定日期范围内表中每次出现同一个人的情况.然后将这个人分组，并将他们的支出总计特定范围.如果他们的消费习惯大于X金额，则在指定的日期范围之间返回此人的每一行.不只是分组的总金额.这是我到目前为止的内容:

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:

SELECT member_id, 
SUM(amount) AS total 
FROM `sold_items` 
GROUP BY member_id 
HAVING total > 50

这将检索正确的总数，并且返回的成员花费超过$ 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.

推荐答案

JOIN带有原始表的此子查询:

JOIN this subquery with the original table:

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.

这篇关于MySQL-分组和总计，但返回每个分组中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！