leetcode Database2 (四)
一、Duplicate Emails
Write a SQL query to find all duplicate emails in a table named Person.
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
For example, your query should return the following for the above table:
+---------+ | Email | +---------+ | a@b.com | +---------+
Note: All emails are in lowercase.
分析：编写一个SQL查询从Person表中找出所有重复的邮箱地址。
解法一：（self join）
# Write your MySQL query statement below select distinct a.Email from Person a, Person b where a.Email=b.Email and a.Id<>b.Id
一开始，写的时候没注意把distinct给漏了，导致出错：
Submission Result: Wrong AnswerMore Details
解法二：
# Write your MySQL query statement below Select Email From Person GROUP BY Email Having count(Email)>1
二、Employees Earning More Than Their Managers
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+----------+ | Employee | +----------+ | Joe | +----------+
分析：题意为雇员表记录了所有雇员的信息，包括他们的经理在内。每一个雇员都有一个Id，和他的经理的Id。
给定雇员表，编写一个SQL查询找出薪水大于经理的员工姓名。对于上表来说，Joe是唯一收入大于经理的员工。
使用自连接：
# Write your MySQL query statement below select m.Name from Employee m,Employee n where m.ManagerId=n.Id and m.Salary>n.Salary;
或：
Select emp.Name from Employee emp inner join Employee manager on emp.ManagerId = manager.Id where emp.Salary > manager.Salary
或：
select a.name from Employee a left join Employee b on a.managerid=b.id where a.salary>b.salary
三、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.
分析：题意为 从员工表中找到工资第二高的数据（即比最高工资少的里面工资最高的）
代码如下：
# Write your MySQL query statement below select max(Salary) from Employee where Salary < (select max(Salary) from Employee);
其他解法：
# Write your MySQL query statement below SELECT Salary FROM Employee GROUP BY Salary UNION ALL (SELECT null AS Salary) ORDER BY Salary DESC LIMIT 1 OFFSET 1
或：
select (select distinct Salary from Employee order by salary desc limit 1,1) as Salary;
注：LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)；offset偏移量
四、Combine Two Tables
able: 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
分析：题意为
有两个数据表：Person表和Address表。Person（人员）表主键为PersonId，Address（地址）表主键是AddressId，通过PersonId与Person表关联。编写一个SQL查询，对于Person表中的每一个人，取出FirstName, LastName, City, State属性，无论其地址信息是否存在。
思路：Person表是主表，Address表是从表，通过Left Outer Join左外连接即可。
# Write your MySQL query statement below select p.FirstName,p.LastName,a.City,a.State from Person p left outer join Address a using (PersonId);
（using()用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用之用于join的条件。）
等价于
# Write your MySQL query statement below select p.FirstName,p.LastName,a.City,a.State from Person p left outer join Address a on p.PersonId=a.PersonId
其他解法：
SELECT FirstName, LastName, City, State FROM Person NATURAL LEFT JOIN Address;
