MySQL 在定义表的位置后抛出错误

Question

我在下面有这个“信息”表，其中有 4 列:'creator_id','viewer_id','date_format','donation'

CREATE TABLE information (
    creator_id INT NOT NULL,
    viewer_id INT NOT NULL,
    date_format DATE NOT NULL,
    donation INT NOT NULL
);

INSERT 
    INTO twitch.information(creator_id,viewer_id,date_format,donation) 
    VALUES
    (10,11,'2014-01-02',34),
    (20,14,'2014-01-02',150),
    (30,15,'2014-01-02',717),
    (31,17,'2014-01-02',177),
    (32,17,'2014-01-06',737),
    (33,16,'2014-01-07',37),
    (40,18,'2016-03-08',442),
    (41,19,'2016-03-09',142),
    (42,10,'2016-03-10',152),
    (43,11,'2016-03-11',512),
    (44,12,'2016-01-12',340),
    (60,0,'2012-01-02',1000),
    (70,1,'2012-01-02',100);

SELECT creator_id,
    MAX(SUM(donation)/COUNT(donation)) AS "TOP AVG DONATION CREATOR ON YEAR 2014 (January)"
    WHERE date_format = "2014-01-02"
    FROM twitch.information;

我正在寻找在“2014-01-02”日期平均捐款最高的 creator_id，但我的输出控制台抛出此错误:错误代码 1064:您的 SQL 语法有误;查看手册对应的...

我认为我的语法有问题，但我不知道是什么。

Answer 1

除了 where 子句必须出现在 from 之后的问题之外，您不需要进行除法(这是有风险的，因为可能出现被零除的异常)。您可以使用 AVG，因此可以通过这种方式找到“在‘2014-01-02’日期平均捐款最高的 creator_id”:

SELECT creator_id,
AVG(donation) AS averageDonation
FROM information
WHERE date_format = "2014-01-02"
GROUP BY creator_id
ORDER BY 2 DESC LIMIT 1;

...或者如果你想要更清楚:

SELECT creator_id,
AVG(donation) AS averageDonation
FROM information
WHERE date_format = "2014-01-02"
GROUP BY creator_id
ORDER BY averageDonation DESC LIMIT 1;