对行进行分组和计数，然后在条件中使用它们

I have an query like:

我有一个查询：

SELECT * FROM account AS a 
LEFT JOIN (SELECT SUM(bill.amount) total, bill.accountId FROM bill GROUP BY bill.accountId) b ON a.id = b.accountId
WHERE a.partner_id = 1 OR a.partner_id = 2

How can I check, how many groups in "bill" has the same a.partner_id? For example: 3 groups has partner_id = 1, 2 groups has partner_id = 2.

如何检查“bill”中有多少组具有相同的a.partner_id？例如：3个组有partner_id = 1，2个组有partner_id = 2。

And later include to left join only groups, if more than 2 groups have the same partner_id.

如果超过2个组具有相同的partner_id，则稍后包括向左连接组。

2 个解决方案

#1

If I understand correctly, you just want an aggregation on top of your query:

如果我理解正确，您只需要在查询之上进行聚合：

SELECT a.partner_id, count(*) as cnt, sum(total) as total
FROM account a LEFT JOIN
     (SELECT SUM(b.amount) as total, b.accountId
      FROM bill b
      GROUP BY b.accountId
     ) b
     ON a.id = b.accountId
GROUP BY a.partner_id;

#2

You should be able to use the "HAVING" clause. Below is an example from the following link: https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

您应该能够使用“HAVING”子句。以下是以下链接中的示例：https：//dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

#1