时间戳

首页 > 解决方案 > 为什么我不能像在支持它的表上那样对递归视图发出相同的查询？

mysql - 为什么我不能像在支持它的表上那样对递归视图发出相同的查询？

问题描述

我有一个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我可以很好地针对表发出这个查询：

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

为什么？我能做些什么呢？

标签： mysqlsqlcommon-table-expression

解决方案

正如@Akina 所说id，是 table 上的键tags，而不是视图上的键。id因此，按照 SQL 标准的规定，不能在视图中为其余列与该列建立直接依赖关系。

你有两个选择。您可以：

  1. 将所有列添加到GROUP BY子句。TEXT有时这对于大型类型（例如二进制文件）是不可能的。

  2. 或者，您可以聚合SELECT子句中的列。

下面是使用后者的修改后的 SQL 语句：

SELECT
  tags_paths.id,
  max(tags_paths.created_at) as created_at,   
  max(tags_paths.updated_at) as updated_at, 
  max(tags_paths.community_id) as community_id, 
  max(tags_paths.tag_set_id) as tag_set_id, 
  max(tags_paths.wiki_markdown) as markdown,
  max(tags_paths.wiki) as wiki, 
  max(tags_paths.excerpt) as excerpt, 
  max(tags_paths.parent_id) as parent_id, 
  max(tags_paths.name) as name, 
  max(tags_paths.path) as path,
  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;

是的，它变得更长了。

推荐阅读