1.sql排名相关问题
-- 排名 分数并列 排名不会并列 1 2 3 4
SELECT
salary,
ROW_NUMBER() over ( ORDER BY salary DESC ) AS 'rank'
FROM
employee
-- 排名 中间会隔数字 列 1 1 3 4
SELECT
salary,
RANK() over ( ORDER BY salary DESC ) AS 'rank'
FROM
employee
-- 排名 中间不间隔数字 列 1 2 2 3
SELECT
salary,
DENSE_RANK() over ( ORDER BY salary DESC ) AS 'rank'
FROM
employee
-- 根据行分区
SELECT
salary,
NTILE(3) over ( ORDER BY salary DESC ) AS 'rank'
FROM
employee
-- 排名 分数并列 排名不会并列 1 2 3 4 5
ROW_NUMBER()
-- 排名 中间会隔数字 列 1 1 3 3 4
RANK()
-- 排名 中间不间隔数字 列 1 1 2 2 3
DENSE_RANK()
-- 根据所有行和 传入参数(列如1,2,3,4) 分区 (个人这么认为)
NTILE(参数)
2. OFFSET 关键字问题
去除第一条数据
SELECT
IFNULL(
( SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1 ), NULL ) AS secondhighestsalary;