在 MySQL 中使用 Group By 将两个查询合并为一个

Question

目前，我正在尝试获取一位用户的所有提交内容以及对特定提交内容的所有评论。我这样做的方式效率极低（循环内查询）。

我被告知要使用 Group By，但我不确定如何让它正常工作。

选择所有提交信息的第一个查询是：

SELECT
    submissions.user_id,
    submissions.id,
    submissions.quote,
    submissions.filename,
    submissions.date_added,
    submissions.views
FROM
    `submissions`
WHERE
    submissions.user_id = ?
ORDER BY
     submissions.date_added
DESC

第二个（获取评论计数）是：

SELECT
    count(id) AS `comments`
FROM
    `comments`
WHERE
    submission_id = '{$subId}'

这些工作，但由于它们会变得很慢，所以我尝试提出这个，但不确定如何正确执行它。

SELECT
    submissions.user_id,
    submissions.id,
    submissions.quote,
    submissions.filename,
    submissions.date_added,
    submissions.views,
    COUNT(comments.id) as `comment`
    comments.submission_id
FROM
    submissions
LEFT JOIN
    comments
ON
    submissions.id=comments.submission_id
WHERE
    submissions.user_id = ?
GROUP BY comment

关于如何解决这个问题有什么建议吗？我最初没有合并前两个查询的原因是因为 count 总是会为整个查询返回一行。

Answer 1

试试这个（我刚刚根据您对错误的评论在 comment 之后添加了一个逗号）：

    SELECT
        submissions.user_id,
        submissions.id,
        submissions.quote,
        submissions.filename,
        submissions.date_added,
        submissions.views,
        COUNT(comments.id) as `comment`,
        comments.submission_id

    FROM
        submissions
    LEFT JOIN
        comments
    ON
        submissions.id=comments.submission_id
    WHERE
        submissions.user_id = ?
    GROUP BY submissions.user_id,
        submissions.id,
        submissions.quote,
        submissions.filename,
        submissions.date_added,
        submissions.views,
        comments.submission_id