如何解决MySQL5.7Error sql_mode=only_full_group_by的问题/

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (260)

在更新到MySQL 5.7.11之后,出现以下报错;

#1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tblslideshow.slideImage'; this is incompatible with sql_mode=only_full_group_by

查询:

SELECT
   tblslideshow.slideImage,
   COUNT(tblslideshow.slideID) AS countVal
FROM
  tblslideshow
WHERE
  tblslideshow.parentID = 3424

目标是返回一个值列表,但也返回所有返回记录的计数。

预期结果应是:

slideImage        |      countVal

Image1.jpg        |      3

Image2.jpg        |      3 

Image3.jpg        |      3 

如何才能实现预期的结果呢?

提问于
用户回答回答于

当使用聚合函数,需要遵守Group by条款:

SELECT
   tblslideshow.slideImage,
   COUNT(tblslideshow.slideID) AS countVal
FROM
  tblslideshow
WHERE
  tblslideshow.parentID = 3424
Group by tblslideshow.slideImage

可以尝试select

SELECT t.slideImage,
(
    SELECT
       COUNT(tblslideshow.slideID) AS countVal
    FROM
      tblslideshow
    WHERE
      tblslideshow.parentID = 3424
) countVal
FROM tblslideshow t
WHERE t.parentID = 3424
用户回答回答于

你要找的是COUNT OVER,可从MySQL 8.0获得:

SELECT
   tblslideshow.slideImage,
   COUNT(*) OVER () AS countVal
FROM
  tblslideshow
WHERE
  tblslideshow.parentID = 3424;

这将保留单个行并向其添加聚合值

扫码关注云+社区

领取腾讯云代金券