SQL的limit用法

1、having

列出所有超过或等于5名学生的课。可能有重复

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math

select class from courses group by class having count(distinct(student)) > 4

2、dateDiff

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前前一天相比温度更高的所有日期的 Id

Id(INT)	RecordDate(DATE)	Temperature(INT)
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

select w1.Id from Weather as w1
left join Weather as w2
on dateDiff(w1.RecordDate,w2.RecordDate) = 1
where w2.RecordDate is not null and w1.Temperature > w2.Temperature

3、内连接

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

Id	Name	Salary	ManagerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	NULL
4	Max	90000	NULL

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

Employee
Joe

select b.Name as Employee
from Employee as b
join Employee as a
on b.ManagerId is not null and a.Id = b.ManagerId
and a.Salary < b.Salary

4、左连接

表1: Person、PersonId 是主键

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar

表2: Address、AddressId 是主键

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

select a.FirstName, a.LastName, b.City, b.State
from Person as a
left join Address as b 
on a.PersonId = b.PersonId