mysql - 优化 MySQL 查询

Question

我想运行一个查询来生成广告系列的收入报告。有2张 table 成员(member)和付款。

members (id, campaign_code)
payments (id, member_id, amount)

我想创建一个按campaign_code分组的表格，即采用这种格式

campaign_code, member_count, total_revenue 

我将所有营销事件放入一个数组中并运行它，

SELECT sum( amount ) AS amt
FROM (members
INNER JOIN payments 
    ON payments.member_id = members.id
)
WHERE campaign_code = 'XX'

这需要很多时间。无论如何要优化它还是在单个查询中完成它？

Answer 1

正如您所说，您需要聚合所有营销事件代码，请尝试此

SELECT m.campaign_code , count(p.member_id) AS member_count,
  SUM( amount ) AS total_revenue
FROM members m, payments p
WHERE p.member_id = m.id
GROUP BY campaign_code;

确保阅读mysql group by function