数据库去除重复数据,需要将数据进行分组,并取出其中一条来展示,group by语句可能会用到。
但是,如果mysql是高版本5.7以上,当执行group by时,如果select查询了多个字段. 而group by分组时却只选择了一个字段时,sql语句就会报错。同时用order by id 之类的,也会报错.原因都一样.报错信息如下:
#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tg_contlist.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
//比如这样会报错
$sql="SELECT id,title,pic,type FROM
`tg_contlist` where $where group by type limit 50 ";
而下面这样就不会了,解决思路就是.让数据库能够有依据去选择没有被分组的那几个字段的值.
$sql="SELECT MAX(id) as id,any_value(title),any_value(pic),`type` FROM
`tg_contlist` where $where group by type limit 50 ";
//max表示选择id最大的那个.any_value表示按排序第一个的来选取
怎么排序呢?因为用了max函数.默认只取一个.排序好像没什么意义??
$sql="SELECT MAX(id) ,any_value(title),any_value(pic),`type` FROM
`tg_contlist` where $where group by type order by MAX(id) desc limit 50 ";//order by id 一 样会报错的.注意了