MYSQL每日2题

1. 第二高薪水  如果不存在则表中字段为null

可以用left join on   ifnull

select ifnull(select distinct salary from employee order by salary desc limit 1,1),null) as secondhighestsalary;

2.第N高薪水

用function函数

create function getNthHighestSalary(N int) returns int

begin

set N=N-1;

   return (

           select ifnull()

              select distinct salary from employee order by salary desc limit N,1),null) 

              as getNthHighestSalary

    );

end

3. 分数排名

rank() over   若出现重复则占用下一个位置   例如  1114

dense_rank()  over  出现重复不占用    例如  1112

row_number() over  不考虑并列排名的情况   1234

select score , dense_rank() over(order by score desc) as rank from scores;

4.group by 和 order by

order by 要 写在group by 后面

并且 order by得列要在 group by里面

例如

select name from stu group by score order by score desc;