方法1:group by去重
方法2:distinct去重
方法3: limit offset
方法4:开窗函数
dense_rank() /rank() /row_number()的选择:
如果有两个并列第一,两个并列第二:
rank()是跳跃排序,结果是1133;
dense_rank()是连续排序,结果是1122;
row_number()连续且唯一,结果是1234。
# #方法1:group by去重
# #如果工资第一多第二多的员工都有多个,所以需要将其按照工资分组再排序
SELECT emp_no,salary
FROM salaries
WHERE salary = (SELECT salary
FROM salaries
GROUP BY salary #group by去重
ORDER BY salary DESC LIMIT 1,1
);
# #方法2:distinct去重
# SELECT emp_no,salary
# FROM salaries
# WHERE salary = (SELECT DISTINCT salary
# FROM salaries
# ORDER BY salary DESC
# LIMIT 1,1
# );
# #方法3: limit offset
# #1.对salary进行倒序排序(desc);2.limit 1 offset 1(返回一行,取第2行)
# SELECT emp_no, salary
# FROM salaries
# order by salary desc
# limit 1 offset 1
# # 方法4:开窗函数
# select t.emp_no, t.salary
# from(
# select emp_no, salary,
# dense_rank() over (order by salary desc) as rk
# from salaries
# )as t
# where rk = 2
# dense_rank() /rank() /row_number()的选择:
# 如果有两个并列第一,两个并列第二:
# rank()是跳跃排序,结果是1133;
# dense_rank()是连续排序,结果是1122;
# row_number()连续且唯一,结果是1234。