mysql - 在没有 GROUP BY 的聚合查询中，SELECT 列表的表达式 #1 包含非聚合列 'jquzntys.posts.id'

Question

我正在尝试按状态对帖子进行计数。但是下面的查询给我这个错误。

Error Message: [42000][1140] In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'jquzntys.posts.id'; this is incompatible with sql_mode=only_full_group_by

SELECT COUNT(p.id)  total,
       p1.authors   authors,
       p2.published published,
       p3.pending   pending,
       p4.scheduled scheduled,
       p5.draft     draft,
       p6.deleted   deleted
FROM posts p
         LEFT JOIN (
    SELECT id, COUNT(*) as `authors`
    FROM posts
    WHERE user_id = ?
) AS p1 ON p1.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `published`
    FROM posts
    WHERE status = ?
) AS p2 ON p2.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `pending`
    FROM posts
    WHERE status = ?
) AS p3 ON p3.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `scheduled`
    FROM posts
    WHERE status = ?
) AS p4 ON p4.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `draft`
    FROM posts
    WHERE status = ?
) AS p5 ON p5.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `deleted`
    FROM posts
    WHERE status = ?
) AS p6 ON p6.id = p.id

没有需要分组的数据。为什么会出现此错误？

Answer 1

如果您使用 COUNT() 但未指定 GROUP BY 子句，它仍然符合聚合查询的条件，但整个结果被视为一个“团体。”您可能认为您没有任何分组，因为您没有 GROUP BY 子句，但它隐含在您对 COUNT() 的使用中。

您似乎想要计算每个状态的帖子数，并显示(任何状态的)帖子总数，还显示特定用户的帖子数。

我建议这样做，使用两个单独的查询:

SELECT COUNT(*) as count
FROM posts
WHERE user_id = ?;

SELECT status, COUNT(*) as count
FROM posts
GROUP BY status WITH ROLLUP;

第二个查询的优点是，如果将来出现任何新的状态值，您不必重写查询。 WITH ROLLUP 为您提供结果集最后一行的总数(在该行，status 将为 NULL)。

并非每个任务都需要在单个 SQL 查询中实现。有时将查询拆分为两个查询可以使两个查询更简单、更清晰。