SQL语句实战

一，leetcode database

1，查找Person表中的重复邮箱

# 需要计算邮箱的个数，因此需要使用group by 对邮箱分组
select email from person group by email having count(email)>1;

2，Employee表中，每个人都有一个ID，对应的有ManagerId，查找出Salary大于ManagerId的Salary的名字

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

# 本表的自然联结
select t1.Name as Employee from Employee as t1,Employee as t2
where t1.ManagerId=t2.Id
and t1.Salary>t2.Salary;

3，Pseson表，Address表如下，要求查出FirstName,LastName,City,State，不论Address存在与否

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

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

# 不论Address有没有都要输出，so，使用左外连接
select FirstName,LastName,City,State from Person as p left outer join Address as a
on p.PersonId=a.PersonId;

4,Customer表，Order表，查出customer表中没有order的用户

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

select Name  from Customers 

where id not in(select customerId from Orders);

5，Weather表，查出温度高于前一天温度的Id

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

# 使用TO_DAYS(DATE)转换为可比较的数字
select t1.Id from Weather as t1,Weather as t2
where t1.Temperature>t2.Temperature and
TO_DAYS(t1.Date)-TO_DAYS(t2.Date)=1;

6,Employee表，找出工资第二高的工资数

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

select MAX(salary) as SecondHighestSalary from Employee where Salary<(select MAX(salary) from Employee);

7，Person表，将重复的email删除，保留Id较小的email

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

delete p1 from Person as p1, Person as p2
where p1.email=p2.email
and p1.Id>p2.Id;