mysql sql_mode 修改 排序分组查询

下载安装的是最新版的mysql5.7.x版本，默认是开启了 only_full_group_by 模式

要MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式需要干掉

1:、    vi /etc/my.cnf

在[mysqld]下面添加如下列：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  

2、set@@sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION   修改对应数据库全局变量

3、any_value（fied）不修改sql_mode 用此函数也可以显示

SELECT aging,any_value(name) FROM cms_forecast_beach  GROUP BY aging ;
+-------+-----------------+
| aging | any_value(name) |
+-------+-----------------+
| 24    | 三亚三亚湾浴场  |
+-------+-----------------+
1 row in set

------------------------------------------------

然后查最新一条记录

SELECT *FROM
 (SELECT *FROM cms_forecast_beach ORDER BY biz_date desc) a
 WHERE name LIKE concat('%三亚三亚湾浴场%') GROUP BY aging ORDER BY biz_date desc;

不知为何此查询 （应该是新版mysql语句解析优化问题）并不能查到最新记录 在旧版本可以~~然后采用以下方法

select fb.* from (select max(biz_date) biz_date, name  FROM cms_forecast_beach WHERE name LIKE concat('%三亚三亚湾浴场%')
group by aging) as tb
left join cms_forecast_beach fb
on tb.name=fb.name and tb.biz_date=fb.biz_date
order by tb.name