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兩個關(guān)鍵字的解法，MySQL中Limit后面的數(shù)字限制了我們返回數(shù)據(jù)的個數(shù)，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，下面語句的邏輯是，假如我們要找第二高的薪水，那么我們允許其中一個最大值存在，然后在其余的數(shù)字中找出最大的，即為整個的第二大的值；

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