mysql - SQL 语句出现错误

Question

我创建了一个适用于 mysql 5.6.26 的 sql 语句，但它不适用于 mysql 5.7.18-0

SELECT AVG(total) as mySum , `id` 
FROM (SELECT total,id 
       FROM `figure` 
       where `userid`=156 ORDER BY id DESC LIMIT 4
     ) t1

我收到的错误:

Error Static analysis:

3 errors were found during analysis.

An expression was expected. (near "(" at position 39) Unexpected token. (near "(" at position 39) This type of clause was previously parsed. (near "SELECT" at position 40) SQL query: Documentation

SELECT AVG(total) as mySum , id FROM (SELECT total,id FROM figure where userid=156 ORDER BY id DESC LIMIT 4) t1

MySQL said: Documentation

1054 - Unknown column 'total' in 'field list'

我做错了什么？数据库结构: id 用户id 总数 1 156 90 2 156 30 3 156 30 4 156 20 5 156 30 6 156 20 7 156 30 8 156 40

Answer 1

您需要将列 id 添加到 GROUP BY 子句，如下所示:

SELECT AVG(total) as mySum , `id` 
FROM (SELECT total, id 
      FROM `figure` 
      WHERE `userid`= 156 
      ORDER BY `id` DESC LIMIT 4
     ) t1
GROUP BY `id`