01.LeetCode组合俩个表

image.png

# Write your MySQL query statement below
select  
    person.FirstName,person.LastName,Address.City,Address.State 
from 
    person 
left join 
    Address 
on 
    person.personId=Address.personId

02.第二高的薪水

image.png

#Oracle可以用nvl代替 ifnull
select 
    ifnull((select distinct(salary) from Employee order by salary desc limit 1,1),null)
as 
    SecondHighestSalary

#oracle 查询排名第二名的数据
select *
from (select name, cj, dense_rank() over(order by cj) pm from xscj s)
where pm = 2

03.第N高薪水

image.png

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull((select distinct Salary 
      from Employee order by Salary desc limit N,1),null) as getNthHighestSalary
  );
END

04.分数排名

image.png

#方法一
select score,dense_rank() over (order by score desc) 'rank'
from scores;
#方法二
SELECT Score,
 (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS 'Rank' 
FROM Scores s 
ORDER BY Score DESC;

https://leetcode-cn.com/problems/consecutive-numbers/comments/