leetcode数据库_SQL语句练习

组合两个表_SQL

注意：如果没有某个人的地址信息，使用 where 子句过滤记录将失败，因为它不会显示姓名信息。

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

---

第N高的薪水_SQL

177. 第N高的薪水

方法一：使用子查询和 LIMIT 子句

DISTINCT去重

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

方法二：使用 IFNULL 和 LIMIT 子句

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

---

实现分数排名

1.select Score,Rank from Scores order by Score desc;
2.

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