时间戳

首页 > 解决方案 > MySQL - ORDER BY and GROUP BY together

mysql - MySQL - ORDER BY and GROUP BY together

问题描述

I am using an external DB and I have 3 important columns: user_id, total_score, score_order.

I would like to get the total_score of each user.

All the scores are always recorded, so I only need the last one. For this I need to use the score_order column.

This is what I am trying to do (using nested queries because I need to combine ORDER BY and GROUP BY):

SELECT * FROM (
    SELECT * FROM `table` ORDER BY score_order DESC
) AS tmp_table GROUP BY user_id

But I get the error:

'#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tmp_table.ranking_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Can someone explain what I am doing wrong?'

标签： mysql

解决方案

在默认的 GROUP BY 模式下，您只能选择 group by 子句中的列，或列上的聚合。

例如，这可能有效：

SELECT user_id, MAX(total_score), MAX(score_order) as score_order_max 
FROM `table` GROUP BY user_id ORDER BY score_order_max DESC;

它可以使用，user_id因为它在 group by 中，而另外两列是聚合。

我认为您不需要双重选择，因为 MySql 不是 oracle。

推荐阅读