MySQL 返回所有相关记录，其中 Count > x 且具有最小值

Question

我对 MySQl 完全陌生，所以请原谅我缺乏行话来解释我需要做什么。我正在尝试弄清楚如何返回相关表的最小值超过 x 次的所有记录。

例如，我正在跟踪多个慈善机构的慈善捐款。我想提取一份向 3 个不同组织捐款至少 25 美元的捐助者名单，每笔合格交易占一行，其中包括所有 3 个表中的 *。

我尝试过将 join 与子查询一起使用，但这要么是错误的方法，要么就是我做错了(很可能!)。

我正在尝试在这里找到的方法:SQL: Select rows with a column value that occurs at least N times? :

SELECT a.*, b.*
  FROM Transactions a
  JOIN (SELECT c.*
      FROM Contributor c
  GROUP BY 
    HAVING COUNT(*) >= 3 and Amount > 25) a.Amount ON b. = a.lname

这是 MySQL sb 的图(使用 MS Access 作为前端):

Answer 1

我想到了什么:

查询1 - 按捐赠者和慈善机构进行聚合，并按金额 >=25 进行过滤，此查询处理捐赠者向同一慈善机构进行超过 25 笔捐赠的可能性

SELECT Transactions.ContributorID, Transactions.OrganizationID
FROM Transactions
WHERE (((Transactions.Amount)>=25))
GROUP BY Transactions.ContributorID, Transactions.OrganizationID;

查询2 - 按捐助者聚合查询1并统计慈善机构

SELECT Query1.ContributorID, Count(Query1.OrganizationID) AS CountOfOrganizationID
FROM Query1
GROUP BY Query1.ContributorID
HAVING (((Count(Query1.OrganizationID))>=3));

查询3 - 显示慈善机构数量 >=3 且金额 >=25 的交易

SELECT Contributor.Company, Organization.CharityName, Transactions.Amount
FROM Query2 INNER JOIN (Organization INNER JOIN (Contributor INNER JOIN Transactions 
ON Contributor.ID = Transactions.ContributorID) 
ON Organization.ID = Transactions.OrganizationID) ON Query2.ContributorID = Transactions.ContributorID
WHERE (((Transactions.Amount)>=24) AND ((Query2.CountOfOrganizationID)>=3));

多合一:

SELECT Contributor.Company, Organization.CharityName, Transactions.Amount
FROM
(SELECT Query1.ContributorID, Count(Query1.OrganizationID) AS CountOfOrganizationID
FROM
(SELECT Transactions.ContributorID, Transactions.OrganizationID
FROM Transactions
WHERE (((Transactions.Amount)>=25))
GROUP BY Transactions.ContributorID, Transactions.OrganizationID) AS
Query1
GROUP BY Query1.ContributorID
HAVING (((Count(Query1.OrganizationID))>=3))) AS
Query2 INNER JOIN (Organization INNER JOIN (Contributor INNER JOIN Transactions ON Contributor.ID = Transactions.ContributorID) ON Organization.ID = Transactions.OrganizationID) ON Query2.ContributorID = Transactions.ContributorID
WHERE (((Transactions.Amount)>=24) AND ((Query2.CountOfOrganizationID)>=3));