mysql - MySQL 中的聚合函数

Question

我遇到了一些关于何时何地可以在 MySQL 中使用聚合函数的问题。如果我有以下两个简单的表:

事件表(campaign_id、campagin_name、帐户、国家/地区)

收入表(campaign_id、收入、日期)

我想编写一个查询来查找每周收入最高的帐户:

我尝试了以下

SELECT account, SUM(revenue) as sum_rev 
FROM campaign 
JOIN revenue 
ON c.campaign_id = r.campaign_id
WHERE revenue = 
( SELECT revenue
  FROM campaign 
  JOIN revenue 
  ON c.campaign_id = r.campaign_id
  WHERE revenue = MAX(SUM(revenue))
)

GROUP BY week(date)

我被告知这是不正确的，问题只是聚合函数 max 和 sum 的嵌套吗？

Answer 1

在MySQL中，我认为变量是最简单的方式:

SELECT cr.*
FROM (SELECT cr.*,
             (@rn := if(@w = concat_ws('-', yyyy, wk), @rn + 1,
                        if(@rn := concat_ws('-', yyyy, wk), 1, 1)
                       )
             ) as rn
      FROM (SELECT c.account, year(r.date) as yyyy, week(r.date) as wk, SUM(r.revenue) as sum_rev 
            FROM campaign c JOIN
                 revenue r
                 ON c.campaign_id = r.campaign_id
            GROUP BY c.account, year(r.date), week(r.date) 
            ORDER BY yyyy, wk, sum_rev DESC
           ) cr CROSS JOIN
           (SELECT @wy := '', @rn := 0) params
     ) cr
WHERE rn = 1;