我有一个 tags 表，我在它上面定义了一个递归cte视图，其中包含tags表的所有列：
CREATE VIEW tags_paths AS
WITH RECURSIVE tag_path (id, created_at, updated_at, community_id, tag_set_id, wiki_markdown,
wiki, excerpt, parent_id, name, path) AS
(
SELECT id, created_at, updated_at, community_id, tag_set_id, wiki_markdown,
wiki, excerpt, parent_id, name, name as path
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.created_at, t.updated_at, t.community_id, t.tag_set_id,
t.wiki_markdown, t.wiki, t.excerpt, t.parent_id, t.name,
concat(tp.name, ' > ', t.name) as path
FROM tag_path AS tp JOIN tags AS t ON tp.id = t.parent_id
)
SELECT * FROM tag_path
ORDER BY path;我可以针对 tags table很好：
SELECT tags.*, COUNT(posts.id) AS post_count
FROM `tags`
LEFT OUTER JOIN `posts_tags` ON `posts_tags`.`tag_id` = `tags`.`id`
LEFT OUTER JOIN `posts` ON `posts`.`community_id` = 2 AND `posts`.`id` = `posts_tags`.`post_id`
WHERE `tags`.`community_id` = 2 AND `tags`.`tag_set_id` = 3
GROUP BY tags.id ORDER BY COUNT(posts.id) DESC LIMIT 96 OFFSET 0;但是，针对 tags_paths 查看：
SELECT tags_paths.*, COUNT(posts.id) AS post_count
FROM `tags_paths`
LEFT OUTER JOIN `posts_tags` ON `posts_tags`.`tag_id` = `tags_paths`.`id`
LEFT OUTER JOIN `posts` ON `posts`.`community_id` = 2 AND `posts`.`id` = `posts_tags`.`post_id`
WHERE `tags_paths`.`community_id` = 2 AND `tags_paths`.`tag_set_id` = 3
GROUP BY tags_paths.id ORDER BY COUNT(posts.id) DESC LIMIT 96 OFFSET 0;作为错误返回，特别是：
[42000][1055] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
'tags_paths.created_at' which is not functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by为什么？我能怎么办？
1条答案
按热度按时间noj0wjuj1#
作为@秋叶树州
idtable上有把钥匙吗tags，但不在视图上。因此，不能在视图中为其余列建立与id列，由sql标准指定。你有两个选择。您可以：
将所有列添加到
GROUP BY条款。有时，对于大型类型（如TEXT或者二进制文件。或者，可以聚合
SELECT条款。下面是使用后者修改的sql语句：
是的，它变长了。