1
SELECT DISTINCT
Salary AS SecondHighestSalary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
如果没有这样的第二最高工资,这个解决方案将被判断为 “错误答案”,因为本表可能只有一项记录。为了克服这个问题,我们可以将其作为临时表。
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
LIMIT 所要取的记录数目(以基准点为参考点) OFFSET 基准点
mysql 的 limit N,M
N 表示起始地
M 表示在限制条数之后的offset条记录。
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 5, 1
就是找到第6高的记录,
1、IF()函数的使用
IF(expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,
如果expr1的值为false,则返回expr3的值。
SELECT IF(TRUE,'A','B'); -- 输出结果:A
SELECT IF(FALSE,'A','B'); -- 输出结果:B
2、IFNULL()函数的使用
IFNULL(expr1,expr2),如果expr1的值为null,则返回expr2的值,
如果expr1的值不为null,则返回expr1的值。
SELECT IFNULL(NULL,'B'); -- 输出结果:B
SELECT IFNULL('HELLO','B'); -- 输出结果:HELLO
3、NULLIF()函数的使用
NULLIF(expr1,expr2),如果expr1=expr2成立,那么返回值为null,
否则返回值为expr1的值。
SELECT NULLIF('A','A'); -- 输出结果:null
SELECT NULLIF('A','B'); -- 输出结果:A
4、ISNULL()函数的使用
ISNULL(expr),如果expr的值为null,则返回1,如果expr1的值不为null,则返回0。
SELECT ISNULL(NULL); -- 输出结果:1
SELECT ISNULL('HELLO'); -- 输出结果:0
2.MySQL - 自动排序函数
-
rank() over
作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。
说明:例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学空出所占的名次。即:1 1 3 4 5 5 7 -
dense_rank() over
作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。
说明:和rank() over 的作用相同,区别在于dense_rank() over 排名是密集连续的。例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学接着下一个名次。即:1 1 2 3 4 5 5 6 -
row_number() over
作用:查出指定条件后的进行排名,条件相同排名也不相同,排名间断不连续。
说明:这个函数不需要考虑是否并列,即使根据条件查询出来的数值相同也会进行连续排序。即:1 2 3 4 5 6
语法格式:row_number() over(partition by 分组列 order by 排序列 desc)
注意: 在使用 row_number() over()函数的时候,over()里面的分组以及排序的执行晚于 where、group by、order by 的执行。
解释:
1.partition by 分组列:按照分组列分割,分组列连续相同的为同一组,1234 123
2.order by 排序列 desc :降序按照排序列排序
3.两个一起用,是先分组,再在分组里面进行排序
row_number() over详解
group by 聚类:结果是聚出来的类,分组并没有将他们聚成一类
使用小提示
dense_rank() over 后面跟排序的依据的列,下面是用了一个排序好的列(order by score desc)。
注意:如果select中有一列是用rank()这类函数,其他的列都会按着他这列规定好的顺序排。(只能放在select里,为生成的一列且语句自己取不到)
select a.score as score ,
(select count(distinct b.score) from Scores b where b.score >= a.score) as 'rank'#这个rank之所以要加引号,因为rank本身是个函数,直接写rank会报错
from Scores a
order by a.score desc
使用dense_rank() over,速度比较快
select score, dense_rank() over (order by score desc) as 'rank' #这个rank之所以要加引号,因为rank本身是个函数,直接写rank会报错
from scores;