表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| SId | varchar(10) | YES | | NULL | |
| CId | varchar(10) | YES | | NULL | |
| score | decimal(18,1) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
按照学生 SId 统计不同学生的总分数并且显示排名
我的 sql 语句
select t1.SId, t1.total_score, count(t2.total_score)+1 rank from
(select score.SId, sum(score) total_score from score group by SId) t1
left join
(select score.SId, sum(score) total_score from score group by SId) t2
on t1.SId = t2.SId and t2.total_score > t1.total_score
order by rank asc
报错提示:
In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 't1.SId'; this is incompatible with sql_mode=only_full_group_by
mysql 版本:5.7.30
网上说更改相关的配置就可以, 但是除了更改相关 mysql 配置还有什么办法更改 sql 语句来解决这个错误呢
我的 sql 语句
select t1.SId, t1.total_score, count(t2.total_score)+1 rank from
(select score.SId, sum(score) total_score from score group by SId) t1
left join
(select score.SId, sum(score) total_score from score group by SId) t2
on t1.SId = t2.SId and t2.total_score > t1.total_score
order by rank asc
报错提示:
In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 't1.SId'; this is incompatible with sql_mode=only_full_group_by
mysql 版本:5.7.30
网上说更改相关的配置就可以, 但是除了更改相关 mysql 配置还有什么办法更改 sql 语句来解决这个错误呢
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.