1.找第二大
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
解法1
SELECT MAX(Salary) as SecondHighestSalary FROM Employee
Where Salary <
(SELECT MAX(Salary) FROM Employee);
解法2
SELECT MAX(Salary) as SecondHighestSalary FROM Employee
WHERE Salary
NOT IN(SELECT MAX(Salary) FROM Employee);
解法3
使用Limit和Offset两个关键字的解法，MySQL中Limit后面的数字限制了我们返回数据的个数，Offset是偏移量，那么如果我们想找第二高薪水，我们首先可以先对薪水进行降序排列，然后我们将Offset设为1，那么就是从第二个开始，也就是第二高薪水，然后我们将Limit设为1，就是只取出第二高薪水，如果将Limit设为2，那么就将第二高和第三高薪水都取出来
SELECT Salary FROM Employee
GROUP BY Salary
UNION ALL (SELECT NULL AS Salary)
ORDER BY Salary DESC LIMIT 1 OFFSET 1;
最后来看一种可以扩展到找到第N高的薪水的方法，只要将下面语句中的1改为N-1即可，第二高的薪水带入N-1就是1，下面语句的逻辑是，假如我们要找第二高的薪水，那么我们允许其中一个最大值存在，然后在其余的数字中找出最大的，即为整个的第二大的值；
SELECT MAX(Salary) FROM Employee E1
WHERE 1 =(SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2WHERE E2.Salary > E1.Salary);
2.Duplicate Emails
注意having 和group by 配合使用
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 |
+---------+
select Email from Person group by Email Having count(*) > 1
