本文共 5647 字,大约阅读时间需要 18 分钟。
注:排名知识点(题目1->思路来源于牛客-小数志(公众号))
本题只需考虑全局第N高的一个,可用order by+limit
本题情况为排名中的第三种同薪同名,总排名连续,所以需要利用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水,由于无法直接用limit N-1,所以需先在函数开头处理N为N=N-1
注1: 这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数(意味着0、负数、小数都不行)或者单一变量(意味着不能用表达式),也就是说想取一条,limit 2-1、limit 1.1这类的写法都是报错的 注2: 这种解法形式最为简洁直观,但仅适用于查询全局排名问题,如果要求各分组的每个第N名,则该方法不适用;而且也不能处理存在重复值的情况CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN SET N:=N-1; ##因为limit不能出现表达式,所以需提前定义,注意要有分号 RETURN ( SELECT salary FROM employee GROUP BY salary ##可能出现同薪,需要分组 ORDER BY salary DESC LIMIT N, 1 );END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( SELECT e.salary FROM employee e WHERE (SELECT count(DISTINCT salary) FROM employee where salary>e.salary) = N-1 ##表示前面有N-1的不同的薪资比第N个薪资大 );END
注: 一般来说,能用子查询解决的问题也能用连接解决
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( SELECT e1.salary FROM employee e1 inner join employee e2 on e1.salary <= e2.salary GROUP by e1.salary HAVING count(DISTINCT e2.salary) = N );END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( SELECT DISTINCT e1.salary FROM employee e1, employee e2 WHERE e1.salary <= e2.salary GROUP BY e1.salary HAVING count(DISTINCT e2.salary) = N );END
注: 以上方法2-4中均存在两表关联的问题,表中记录数少时尚可接受,当记录数量较大且无法建立合适索引时,实测速度会比较慢,用算法复杂度来形容大概是O(n^2)量级(实际还与索引有关)。那么,用下面的自定义变量的方法可实现O(2*n)量级,速度会快得多,且与索引无关。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( SELECT DISTINCT salary FROM (SELECT salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary FROM employee, (SELECT @r:=0, @p:=NULL)init ORDER BY salary DESC) tmp WHERE rnk = N );END
本题将查询语句封装成一个自定义函数并给出了模板,实际上是降低了对函数语法的书写要求和难度,而且提供的函数写法也较为精简。然而,自定义函数更一般化和常用的写法应该是分三步:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN # i 定义变量接收返回值 DECLARE ans INT DEFAULT NULL; # ii 执行查询语句,并赋值给相应变量 SELECT DISTINCT salary INTO ans FROM (SELECT salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary FROM employee, (SELECT @r:=0, @p:=NULL)init ORDER BY salary DESC) tmp WHERE rnk = N; # iii 返回查询结果,注意函数名中是 returns,而函数体中是 return RETURN ans;END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( SELECT DISTINCT salary FROM (SELECT salary, dense_rank() over(ORDER BY salary DESC) AS rnk FROM employee) tmp WHERE rnk = N );END
窗口函数
select Score,dense_rank() over(order by Score desc) as 'Rank'##必须加引号,否则会出现错误from Scores;
select a.Score Score, (select count(distinct b.score) from Scores b where b.score>=a.Score ) 'Rank' ##排名的思路,需要找出a.Score前面有N个大于他的,a.Score的排名则是N+1,也就是大于a.Score不同分数的个数,可以再纸上举出一个小例子,即可明白,例如43321,1是第4名,distinct 4:1个,distinct 3:1个,distinct 2:1个,用>=也就是1所在的第4名FROM Scores aorder by a.Score desc
##### 思路:先查找Employee表中的部门中的最高薪资,在连接两个表。
select Department.Name as Department, Employee.Name as Employee, Salaryfrom Employee inner join Departmenton Department.ID = Employee.DepartmentIdwhere (Employee.DepartmentId,Salary) IN (SELECT DepartmentId,MAX(Salary)##可以先查找部门内的最高薪资,在Employee表,然后再去查找名字,连接两个表 FROM Employee GROUP BY DepartmentId)
SELECT (CASE WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1 ##奇数且不是最后一个座位,则和下一个id交换 WHEN MOD(id, 2) != 0 AND counts = id THEN id ##如果学生人数是奇数,则不需要改变最后一个同学的座位 ELSE id - 1##偶数,则id-1和奇数交换 END) AS id, studentFROM seat, (SELECT COUNT(*) AS counts FROM seat) AS seat_counts ##查找座位的个数ORDER BY id ASC;
SELECT s1.id, COALESCE(s2.student, s1.student) AS student ##返回第一个非空的表达式FROM seat s1 LEFT JOIN seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id ##异或,就是^两边,第一次相加,第二次相减,以此类推ORDER BY s1.id;
Select distinct L1.Num ConsecutiveNums ##必须写distinct ,只输出1个值from Logs L1,Logs L2, Logs L3where L1.ID=L2.ID+1 AND L2.ID=L3.ID+1 AND L1.Num=L2.Num and L2.Num=L3.Num
转载地址:http://fvzki.baihongyu.com/