重写版本5.7中的sql

4si2a6ki  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(287)

我有一个sql,它在MySQL5.6版本中工作,但在MySQL5.7.x版本中正在崩溃。

SELECT * FROM (SELECT * FROM photos WHERE photoable_type = 'Mobilehome'
AND photoable_id IN (SELECT id FROM mobilehomes WHERE
mobilehomes.community_id = 1) AND photos.image_file_size IS NOT NULL 
AND photos.is_published IS TRUE ORDER BY photos.priority ASC) AS tmp_table
GROUP BY photoable_id

它给我带来了以下错误:

Expression #1 of SELECT list is not in GROUP BY clause and contains
   nonaggregated column 'tmp_table.id' which is not functionally 
    dependent on columns in GROUP BY clause; this is 
    incompatible with sql_mode=only_full_group_by
jdgnovmf

jdgnovmf1#

在这种情况下,或者您将instrcut数据库的sql模式更改为mysql 5.6版本,或者您可以将您的查询指定为新的行为
在第二种情况下,如果您使用group by whithout聚合函数,这意味着对于所有不同于photoable\ U id的列,您接受偶然的结果
这意味着您可能还可以接受一个基于聚合结果的greagation函数,例如:on min()或max()
假设你的表包含col1,col2。。必须明确声明所需的列

SELECT photos.photoable_id, min(col1), min(col2),.... 
  FROM photos 
  INNER JOIN mobilehomes ON mobilehomes.community_id = 1 
      AND photos.photoable_type = 'Mobilehome'
        AND photos.photoable_id = mobilehomes.id
  AND photos.image_file_size IS NOT NULL 
  AND photos.is_published IS TRUE 

  GROUP BY photos.photoable_id
  ORDER BY photos.priority ASC

查看代码似乎也可以避免子查询

相关问题