mysql - 如何从表格中获取最多评论的用户

Question

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

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); 

标签： mysqlsqldatabase

Answer 1

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
);