按在地上摩擦面试官mysql之实战七

1. 聚合操作利用group by配合avg

2. round(n,m) n数值，m保留几位有效数字

select job,round(avg(score),3)as v from grade group by job order by v desc

1. 先找到每个job的平均分

2. 关联比较同一job分数大于平均分的

select g.id, g.job, score from grade g join (select job, avg(score) avg1 from grade group by job)s

on g.job = s.job

where score > avg1

order by id

1. 利用窗口函数 dense_rank() over

2. 关联language找到name

3. 将上面的结果作为临时表，取前两名添加rn条件<3

select id,name,score

from (select g.id,l.name,g.score,

dense_rank() over(partition by g.language_id order by score desc) rn from

grade g inner join language l on g.language_id=l.id) t

where rn<3

order by name,score desc,id;

1. 此题是一道数学题，知道中位数，那么sql自然而然就会写了

2. 假设1个岗位人数总数为N，那么不管是奇数还是偶数，起始位置一定是N/2

3. 如果是奇数，那么终止位置也是N/2，如果是偶数，终止位置就是N/2+1，所以后面的+1判断是不是偶数就行了

select job,floor((count(id)+1)/2) as 'start',ceil((count(id)+1)/2) as 'end'

from grade

group by job

order by job asc

1. 中位数本身就是先按自然数排名，再取数。排序函数选用row_number

2. 用一条规则统一奇数个数时和偶数个数时的中位数位置。无论奇偶，中位数的位置距离（个数+1）/2 小于1

select id,job,score,r from

(select *,rank()over(partition by job order by score desc)as r,count(*)over(partition by job)as t

from grade) as A

where round(abs(r-(t+1)/2)*1.0,2)<1

order by id;

1. over窗口函数的使用

select grade,

sum(number)

over(order by grade) t_rank

from class_grade;

1. 当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数

A A B B C C D D 

1 2 3  4  5 6  7 8

8 7 6  5  4  3 2 1

那么上面的4，5以及5，4就是中位数，如果是奇数的话，就只有1个

2. 求逆序和其实就是 sum(a) over (order by b desc) 就行了，多加了一个desc

select grade from (

    select grade

            ,(select sum(number) from class_grade) as 'total'

            ,sum(number) over(order by grade) a

            ,sum(number) over(order by grade desc) b

     from class_grade) t1

where a >= total/2 and b >=total/2

order by grade

1. 分组，求和，倒序取一条

select u.name, sum(g.grade_num) as s

from user u

inner join grade_info g

on u.id = g.user_id

group by u.name

order by s desc

limit 1;

1. 根据上题我们能轻松的找到最大的积分总和

2. 再根据最大积分总和找到对应的user_id和总积分 group by 不能跟where

3. 再关联user表找name

select user.id, user.name,t.grade_sum from(

select user_id,sum(grade_num) as grade_sum from grade_info group by user_id

having grade_sum=(select sum(grade_num) as s from grade_info group by user_id order by s desc limit 1)

) t

join user

on t.user_id=user.id

order by user.id  

1. 与前一题基本差不多，唯一不同就是分数的计算

2. 使用case when判断分数是加还是减

select user.id, user.name,t.grade_sum from(

select user_id,sum(case type when 'add' then grade_num else -grade_num end) as grade_sum from grade_info group by user_id

having grade_sum=(select sum(case type when 'add' then grade_num else -grade_num end ) as grade_sum from grade_info group by user_id order by grade_sum desc limit 1)

) t

join user

on t.user_id=user.id

order by user.id