易错点分析

1.初始（错误）代码

select uid,exam_id,round(avg(new_score),0) as avg_new_score
from 
    (select uid,er.exam_id,
     if(count(score)=1,score,
      100*(score-min(score))/(max(score)-max(score))) as new_score
    from exam_record as er
    join examination_info as ei
    on er.exam_id=ei.exam_id
    where difficulty='hard' and score is not null
    group by exam_id) as q
group by uid,exam_id
order by exam_id asc,avg_new_score desc

• 报错：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'er.uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• 分析：

若new_score要用聚合函数count()min()max()，所取其他变量中uid非聚合列，与group by exam_id无关

2.更正

• 更正方法：

min() over(partition by col1)——不改变表的结构而在每行增加一最小值列

注： 括号内不要加order by，即不要用min() over(partition by col1 order by col2),不然是逐一取最小值，即第一行得到的是1个数据的最小值，第二行得到的是前两个数据中的最小值。

• 最终代码：

select uid,exam_id,round(avg(new_score),0) as avg_new_score
from 
    (select uid,er.exam_id,
     if(max(score)over(partition by exam_id)=
        min(score)over(partition by exam_id),score,
        100*(score-min(score)over(partition by exam_id))/(
            max(score)over(partition by exam_id)-min(score)
            over(partition by exam_id))) as new_score
    from exam_record as er
    join examination_info as ei
    on er.exam_id=ei.exam_id
    where difficulty='hard' and score is not null) as q
group by uid,exam_id
order by exam_id asc,avg_new_score desc