【leetcode】sql练习

以前数据库没有好好上，补一下这方面的基础知识。

175. Combine Two Tables

Table: Person

+————-+———+
| Column Name | Type |
+————-+———+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+————-+———+
PersonId is the primary key column for this table.

Table: Address

+————-+———+
| Column Name | Type |
+————-+———+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+————-+———+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State
连接两个表， 这里我用left join … on

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

这个答案对于三种情况进行了总结

176. Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+—-+——–+
| Id | Salary |
+—-+——–+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+—-+——–+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

一个是distinct 用来区别不同的元素，合并相同的元素
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
DESC倒序
limit用来选取需要的元素
Your runtime beats 78.48% of mysqlsubmissions.

SELECT distinct(Salary) as SecondHighestSalary FROM Employee UNION SELECT NULL ORDER BY SecondHighestSalary DESC LIMIT 1,1 

Write a SQL query to get the nth highest salary from the Employee table.

177. Nth Highest Salary

这里面倒是函数的用法，奇怪的是没有对null进行处理反而对了。

+—-+——–+
| Id | Salary |
+—-+——–+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+—-+——–+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
还有一个点就是limit不能用n-1这样的存在。

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