mysql - 如何从表中获得最多注释的用户

我正在尝试获得评论最多的用户名。我怎样才能做到这一点？
这是表格。
下表是我要查询的数据库表的设置。

CREATE TABLE User(
        userid varchar(3),
        firstname varchar(20),
        lastname varchar(20),
        age int,
        PRIMARY KEY(userid)
    )ENGINE=INNODB;


    CREATE TABLE Comment(
        commentid varchar(3),
        userid varchar(3),
        eventid varchar(3),
        title varchar(20),
        comment varchar(50),
        PRIMARY KEY(commentid),
        FOREIGN KEY(userid) REFERENCES AnonymousUser(userid),
        FOREIGN KEY(eventid) REFERENCES Event(eventid)
    )ENGINE=INNODB;


    INSERT INTO User VALUES('U01','Charles','Darwin',99);
    INSERT INTO User VALUES('U02','Keisha','Strawn',24);
    INSERT INTO User VALUES('U03','Denise','Malcolm',59);
    INSERT INTO User VALUES('U04','Dennis','Stewart',19);
    INSERT INTO User VALUES('U05','Robert','Johns',45);
    INSERT INTO User VALUES('U06','Marsha','Stewart',33);

    INSERT INTO Comment VALUES ('C01','A01','E01','Boring Event','This event was boring');
    INSERT INTO Comment VALUES ('C02','A02','E01','Nice Nice Event','This event was Nice');
    INSERT INTO Comment VALUES ('C03','A03','E03','Wow','This event was Amazing');
    INSERT INTO Comment VALUES ('C04','A01','E01','Very Sad','I missed this event');

我试过的查询是

SELECT User.userid FROM User
JOIN comment ON comment.userid = user.userid
WHERE (SELECT COUNT(comment)
FROM comment = (SELECT MAX(userid) FROM comment);

最佳答案

SELECT
    userid
FROM
    comment
GROUP BY userid
ORDER BY count(userid) DESC
LIMIT 1;

编辑：哦，你需要用户名。尝试这个：

SELECT firstname
FROM user
WHERE userid = (
    SELECT
        userid
    FROM
        comment
    GROUP BY userid
    ORDER BY count(userid) DESC
    LIMIT 1
);

关于mysql - 如何从表中获得最多注释的用户，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55269722/