查找第n高的数据
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
Employee表:
Id | Salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
输出结果:
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
SecondHighestSalary |
---|
200 |
方法一:利用子查询,找出除开最高工资外的,其他工资中的最高工资
SELECT MAX(DISTINCT Salary) SecondHighestSalary //成绩可能有重复,用distinct去重
FROM Employee
WHERE Salary <> (SELECT MAX(DISTINCT Salary) FROM Employee );
//改成WHERE Salary < (SELECT MAX(DISTINCT Salary) FROM Employee ); 结果一样
方法二:用IFNULL 和 LIMIT 子句
LIMIT子句用于限制查询返回结果的数量
'''返回数据表自索引i(i默认从0开始)开始的n条数据'''
SELECT * FROM tablename LITMIT i,n
IFNULL子句
返回一个数字或字符串值
IFNULL(expr1,expr2)
若expr1的值不为空值NULL,则返回expr1,为空则返回expr2
SELECT IFNULL(
(SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC
LIMIT 1,1),NULL
) AS SecondHighestSalary
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。如果不存在第 n 高的薪水,那么查询应返回 null。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE P INT DEFAULT N-1;
IF (P<0) THEN
RETURN NULL;
ELSE
RETURN (
SELECT IFNULL(
(
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT P, 1
), NULL
) AS SecondHighestSalary
);
END IF;
END
笛卡尔积
找出超过经理收入的员工。Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
Id | Name | Salary | ManagerId |
---|---|---|---|
1 | Joe | 70000 | 3 |
2 | Henry | 80000 | 4 |
3 | Sam | 60000 | NULL |
4 | Max | 90000 | NULL |
SELECT e2.Name AS Employee
From Employee AS e1
JOIN Employee AS e2
WHERE e1.Id=e2.ManagerId AND e2.Salary>e1.Salary
查找重复数据
查找 Person 表中所有重复的电子邮箱。
看到“找重复”的关键字眼,首先要用分组函数(group by),再用聚合函数中的计数函数count()给姓名列计数。
Id | |
---|---|
1 | a@b.com |
2 | c@d.com |
3 | a@b.com |
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email)>1
删除重复的数据
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
Id | |
---|---|
1 | john@example.com |
2 | bob@example.com |
3 | john@example.com |
Id 是这个表的主键。
方法一: 自连接+ WHERE
DELECT p1
FROM Person p1,Peroson p2
WHERE p1.email=p2.email AND p1.Id>p2.Id
方法二:NOT IN + 子查询
正确解法:
DELETE FROM Person
WHERE Id NOT IN(
SELECT Id FROM (SELECT MIN(Id) AS Id FROM Person GROUP BY Email) AS temp
)
错误解法:不能在WHERE查询语句中改变所要修改的表格,应该SELECT为另一个表
DELETE FROM Person
WHERE Id NOT IN(
SELECT MIN(Id) FROM Person
GROUP BY Email
)
查找不存在的数据
- 某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers 表:
Id | Name |
---|---|
1 | Joe |
2 | Henry |
3 | Sam |
4 | Max |
Orders 表:
Id | CustomerId |
---|---|
1 | 3 |
2 | 1 |
方法一:用 LEFT JOIN连接两表
SELECT name AS Customers
FROM Customers c
LEFT JOIN Orders o
ON c.Id=o.CustomerId
WHERE o.CustomerId IS NULL
// MySql检验是否为空值不能用 a=NULL,而是a IS NULL
方法二:子查询 + NOT IN
SELECT Customers.Name AS Customers
FROM Customers
WHERE Id NOT IN (
SELECT DISTINCT CustomerId FROM Orders
)
方法二:子查询 + NOT EXIST
SELECT c.Name Customers
FROM Customers c
WHERE NOT EXISTS( SELECT o.CustomerId FROM Orders o WHERE c.id=o.CustomerId )