1、select (select distinct salary from employee order by salary desc limit 1,1) as SecondHighestSalary
as:给数据库字段取别名。
distinct:排除相同得数据字典
order by 排序,模式从小到大,desc是降序。
limit:指标查询表格行列。1,1表示第二行的第1行的字段
select ifnull((select distinct salary from employee order by salary desc limit 1,1) ,null) as SecondHighestSalary
ifnull(a,b)表示数据a为空,则选择输出b
上述例子升华:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select ifnull((select distinct salary from Employee order by salary desc limit N,1),null)
);
END
set设置变量的值
RETURN代表语句结果
2、分数表排名
方法一:
select
a.score,
count(distinct b.score) 'rank'
from
scores a,scores b
where a.score <= b.score
group by a.id
order by a.score desc
首先明白笛卡尔积,其次sql执行顺序1、最先执行from tab;2、where语句是对条件加以限定;3、分组语句【group by…… having】;4、聚合函数;5、select语句;6、order by排序语句
方法二:使用函数
rank()
排名为相同时记为同一个排名, 并且参与总排序
dense_rank() over (PARTITION BY xx ORDER BY xx [DESC])
排名相同时记为同一个排名, 并且不参与总排序
row_number() over (over (PARTITION BY xx ORDER BY xx [DESC]))
排名相同时记为下一个排名
select score,dense_rank() over (order by score desc)'Rank' from Scores
暂定。。存入自己word里面了