一,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;