leetcode 数据库刷题

    数据库比较薄弱，基本啥都不会，刷题提高一下，写篇博客记录一下。ps:leetcode appreciate

175. Combine Two Tables简单的左联问题  

# 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

# left right inner 都要了解以及熟悉

176. Second Highest Salary 

# Write your MySQL query statement below
# select  NULLIF(Salary, '')  as SecondHighestSalary 
# from Employee 
# order by Salary desc Limit 1 offset 1
# 为什么我这个不行？？
select (
  select distinct Salary from Employee order by Salary Desc limit 1 offset 1
)as SecondHighestSalary

# distinct 遇到空字符串，默

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m int ;
set m = N - 1 ;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee order by Salary desc limit 1 offset m
  );
END

# 注意limit offset 用法 limit n ; limit n , m ; limit n offset m ; 这几个用法的区别

178. Rank Scores

# Write your MySQL query statement below
# select score , (@i := @i + 1) from Scores,(select @i := 0) init order by score desc

# SELECT
#   Score,
#    @rank := @rank + (@prev != (@prev := Score))  as Rank 
#    # 这个Rank起到重命名作用 <> 和 != 相等的 不等的话就会返回1
# FROM
#   Scores,
#   (SELECT @rank := 0, @prev := -1) init
# #   用SELECT 来给变量赋值 初始化
# ORDER BY Score desc 

select 
    Score , @rank := @rank + (@pre != (@pre := Score)) as Rank
from
    Scores ,
    (select @rank := 0 , @pre := -1) init 
order by Score desc    
    

177. Nth Highest Salar

177. Nth High