力扣_sql（自学）

175. 组合两个表

select FirstName,LastName,City,State from Person
left join Address
on Person.PersonId = Address.PersonId;

176. 第二高的薪水

select 
    ifnull(
        (
            select distinct salary from Employee
            order by salary desc
            limit 1 offset 1
        ),null
) as SecondHighestSalary;

177. 第N高的薪水

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 1 offset N
          ),null
      ) as getNthHighestSalary
  );
END

178. 分数排名

select a.score as score,
(select count(distinct b.score) from Scores b where b.score >= a.score) as 'rank'
from Scores a
order by a.score desc;

181. 超过经理收入的员工

select name as Employee from Employee a
where a.salary > (select salary from Employee b where b.id = a.managerId);

select a.name as Employee
from Employee a,Employee b
where a.managerId = b.id
and a.salary > b.salary;