网站建设 大公司小公司/广告推广免费

关于数据库中写sql的问题，感觉最常见的就是薪水方面的

拿到一道sql题，首先感觉一下，可能会出现的问题，比如这道题，薪水问题，薪水会不会出现同名同薪的问题？？要怎么考虑？ 又比如mysql怎么写？如果是orcale呢？oracle有窗口函数，那么mysql呢？

排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：
同薪不同名，排名类似于编号，连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4
同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4
同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3。
不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。
本题的目标是实现第三种排名方式下的第N个结果，且是全局排名，不存在分组的问题，实际上还要相对简单一些。

所以拿到一个有排名问题的sql题，还需要考虑，需要不需要分组排序？ 还是说是全局排序？ 使用 order by 还是group by？ 还是组合使用？

思路1：单表查询
由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：

同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。
注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。
注：这种解法形式最为简洁直观，但仅适用于查询全局排名问题，如果要求各分组的每个第N名，则该方法不适用；而且也不能处理存在重复值的情况。

思路2：窗口函数，其实就是一个范围，就是先规定了一个范围。
实际上，在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：
主要涉及到的函数就是

1. row_number():同薪不同名的，3000，2000，2000，1000，排名就是，1，2，3，4；
2. rank()：同薪同名，但是跳级的，也就是不连续，3000，2000，2000，1000，排名就是，1，2，2，4；
3. dense_rank():同薪同名且不跳级，也就是连续的：3000，2000，2000，1000，排名就是，1，2，2，3；
4. ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用.

而我们这题，需要使用是dense_rank(),另外这三个函数必须要与其搭档over()配套使用，over()中的参数常见的有两个，分别是
partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据。

要注意版本，以下是8.0 才支持的。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGINRETURN (# Write your MySQL query statement below.  select DISTINCT salary(select salary,dense_rank() over(order by salary desc) as rnkfrom Employee) tmpWHERE N = rnk;);
END

至此，可以总结MySQL查询的一般性思路是：

能用单表优先用单表，即便是需要用group by、order by、limit等，效率一般也比多表高

不能用单表时优先用连接，连接是SQL中非常强大的用法，小表驱动大表+建立合适索引+合理运用连接条件，基本上连接可以解决绝大部分问题。但join级数不宜过多，毕竟是一个接近指数级增长的关联效果

能不用子查询、笛卡尔积尽量不用，虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程，但效率仍然难以保证

自定义变量在复杂SQL实现中会很有用，例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现

如果MySQL版本允许，某些带聚合功能的查询需求应用窗口函数是一个最优选择。除了经典的获取3种排名信息，还有聚合函数、向前向后取值、百分位等，具体可参考官方指南。