leetcodeデータベースブラシ

データベースは比較的に弱くて、基本的に何もできなくて、問題をブラシして少し高めて、ブログを書いて記録します.ps:leetcode appreciate
175.Combine Two Table簡単な左連問題

# 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