MySQL单表操作面试题

1. 求所有学生科目分数高于80分的学生姓名及成绩。

结果：

select name,chengji from t_table group by name having min(chengji) > 80 

2.按年月分别展示数据

select year,
sum(case when month = 1 then mon else 0 end) as m1,
sum(case when month = 2 then mon else 0 end) as m2,
sum(case when month = 3 then mon else 0 end) as m3,
sum(case when month = 4 then mon else 0 end) as m4
from t_table group by year

3.删除表中重复的数据，并且保留一条

delete from t_table where name in (
    select name from t_table group by name having count(name)>1
) and id not in(
    select id from t_table group by name having count(name)>1
)

4.对一个班的同学分组，并且对每个班的学生进行排序

答：

SELECT (@i := CASE WHEN @pre_times=banji then @i + 1 ELSE 1 END )rownum,b.*,@pre_times:=banji as newBanji from
(SELECT id,name,banji FROM t_banjiName  GROUP BY banji,name ) b,
(SELECT @i := 0,@pre_times :='')as a