LeetCode上的几道数据库题目

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即可，不会看我转载的这篇文章：数据库表的连接(Left join , Right Join, Inner Join)用法详解

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

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.

　　感觉题目并没有说用SecondHighestSalary作为别名啊，一直错，看了Discuss才知道。

select max(Salary) as SecondHighestSalary from Employee where Salary < (select max(Salary) from Employee)

　　利用不存在的话max会返回null，所以不需要单独判断。

Nth Highest Salary

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

+—-+——–+
| 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.

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

　　关键是limit的使用，limit m,n是指从第m+1条开始（包括它），取n条。