仅mysql\u full\u group by错误

zdwk9cvp  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(482)

这个问题在这里已经有答案了

在mysql中执行查询时,仅与\u full \u group \u by相关的错误(18个答案)
两年前关门了。
我不是dba,我继承了这个查询,我不太清楚为什么我会得到一个 sql_mode=only_full_group_by 错误。尤其是因为这个错误 result.fullURL 我在查询中没有看到。有人能给我解释一下吗?
查询

SELECT *
FROM (
    SELECT content.*, content.navigationOrder AS sortOrder
    FROM LB_Content AS content
    INNER JOIN LB_Content AS other ON content.contentSectionId = other.contentSectionId
    WHERE other.fullURL = '/index'
    AND content.contentSlug <> 'index'
    GROUP BY content.contentId
    UNION
    SELECT content.*, query.sectionOrder AS sortOrder
    FROM LB_Content AS content, (
        SELECT section.*
        FROM LB_ContentSections AS section
        INNER JOIN LB_Content AS other ON section.parentContentSectionId = other.contentSectionId
        WHERE other.fullURL = '/index'
    ) AS query
    WHERE content.contentSectionId = query.contentSectionId
    AND content.contentSlug = 'index'
) as result,
LB_ContentTypes AS types
WHERE result.showInNavigation = 1
AND result.status = 1
AND result.published = 1
AND result.contentTypeId = types.contentTypeId
AND types.useOption = 1
GROUP BY result.contentId
ORDER BY result.sortOrder ASC

错误输出
sqlstate[42000]:语法错误或访问冲突:select list的1055表达式#2不在group by子句中,并且包含未聚合的列“result.fullurl”,该列在功能上不依赖group by子句中的列;这与sql\u mode=only\u full\u group by不兼容
谢谢你的帮助!

zaq34kh6

zaq34kh61#

“only full group by”表示聚合查询必须在结果中的所有非聚合字段上进行分组。它们是否“隐藏”在 * ,这些字段也必须按分组。
这个查询甚至不应该从分组开始;没有使用聚合函数。如果“only full group by”不是当前设置,则此查询将是“为每个contentid提供一个有效的随机结果,传递where条件”。

相关问题