MySQL 嵌套优化

Question

我已经尝试解决这个问题两个小时了，但没有成功

下面附上对此查询的响应:

SELECT login, SUM(gain) AS weekly_gain
FROM gt_history
WHERE is_closed = 1 AND (cmd = 0 OR cmd = 1)
GROUP BY WEEK(close_time), login

现在，我想做的是，对所有值进行平均，GROUP BY登录。

想法？

Answer 1

只需使用您的查询作为子查询。

SELECT login, AVG(weekly_gain) FROM
    (SELECT login, SUM(gain) AS weekly_gain
    FROM gt_history
    WHERE is_closed = 1 AND (cmd = 0 OR cmd = 1)
    GROUP BY WEEK(close_time), login)
GROUP BY login