力扣学习计划：SQL语句_力扣sql刷题 建表语句

• 学习计划地址

---

---

第一天：选择

1.1、大的国家

题目链接

select 
	name,population,area 
from 
	World 
where
	area >= 3000000 or population >= 25000000
1
2
3
4
5
6

---

1.2、可回收且低脂的产品

题目链接

select 
	product_id 
from 
	Products 
where 
	low_fats = 'Y' and recyclable = 'Y'
1
2
3
4
5
6

---

1.3、寻找用户推荐人

题目链接

select 
	name 
from 
	customer 
where 
	referee_id != '2' or referee_id is null
1
2
3
4
5
6

---

1.4、从不订购的客户

题目链接

select c.Name as Customers 
    from Customers c
where c.Id not in 
    (select CustomerId from Orders)
1
2
3
4

---

第一天加餐

1.5、超过经理收入的员工

题目链接

select 
    a.name as Employee 
from 
    Employee a,Employee b
where 
    a.managerId = b.id 
and 
    a.salary > b.salary
1
2
3
4
5
6
7
8

---

1.6、第二高的薪水

题目链接

方法一：使用子查询和 LIMIT 子句

SELECT * FROM table LIMIT 2,1; 			// 跳过2条数据读取1条数据，即读取第3条数据
SELECT * FROM table LIMIT 2 OFFSET 1;   // 跳过1条数据读取2条数据，即读取第2-3条数据
1
2

将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资

SELECT 
	DISTINCT Salary AS SecondHighestSalary FROM Employee
ORDER BY 
	Salary DESC LIMIT 1,1
1
2
3
4

然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表。

SELECT
    (SELECT DISTINCT Salary FROM Employee
     ORDER BY Salary DESC LIMIT 1,1)
AS SecondHighestSalary;
1
2
3
4

---

方法二：使用 IFNULL 和 LIMIT 子句

IFNULL(a,b)，如果a的值为null，则返回b的值，如果a的值不为null，则返回a的值。

解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数，如下所示。

select IFNULL(
   (select DISTINCT salary from Employee 
    order by salary desc limit 1,1) 
, null) as SecondHighestSalary 
1
2
3
4

---

第二天：排序&修改

2.1、计算特殊奖金

题目链接

---

解法一：IF

select 
	employee_id,
if 
	(employee_id %2 = 1 and name not like 'M%', salary, 0) as bonus
from 
	Employees ORDER BY employee_id;
1
2
3
4
5
6

解法二：case when 条件 then v1 else v2 end 条件满足输出 v1 否则 v2

SELECT 
    employee_id, 
CASE WHEN 
    employee_id % 2 = 1 AND name not like 'M%' THEN salary ELSE 0 END AS bonus 
FROM 
    Employees ORDER BY employee_id;
1
2
3
4
5
6

拓展：case 字段 when k1 then v1 else v2 ：当字段值为 k 时输出 v1 否则 v2

---

2.2、变更性别

题目链接

---

两种解法：

update salary set sex = case sex when 'm' then 'f' else 'm' end;

update salary set sex = if(sex='m','f','m');
1
2
3

---

2.3、删除重复的电子邮箱

题目链接

DELETE p1 
    from Person p1,Person p2 
where 
    p1.email = p2.email and p1.id > p2.id
1
2
3
4

---

第二天加餐

2.4、查找重复的电子邮箱

题目链接

解法一：临时表

select p.Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as p
where p.num > 1
1
2
3
4
5
6
7

解法二：having函数

select Email from Person group by Email having Count(Email) > 1
1