一. 使用过程
Query 1 ERROR: Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'ai.ln_owners.name' which is not functionally dependent on columns
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT
`ln_housing_id`,
`name`
FROM
`ln_owners`
ORDER BY
`id` DESC
GROUP BY
`ln_housing_id`
- 这种又会出现什么样的错误呢，我们看下 【这次是语法错误】：
Query 1 ERROR: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'GROUP BY
`ln_housing_id`' at line 8
二、 我们该如何正确使用 GroupBy 和 OrderBy 呢
SELECT
ln_housing_id,
aid,
lasted
FROM (
SELECT
`ln_housing_id`,
ANY_VALUE(`id`) AS aid,
ANY_VALUE(`updated_at`) AS lasted
FROM
`ln_owners`
GROUP BY
`ln_housing_id`) AS tb
ORDER BY
tb.lasted DESC
-
groupBy 里面子句查询如果在你开启 sql_mode=only_full_group_by 聚合列索引检查，就会出错
- 开启的话，我们使用 ANY_VALUE('id') 聚合一下其他列 ，然后通过子查询的派生表 再去外层进行降序排列
- 如果groupBy 的字段是主键（primaryKey） 或者 唯一不为 NULL 列，这个是可以不用聚合列的，可以查询所有，因为列字段没有可选性
