1.leetcode(596)
题目:有一个courses
表 ,有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
应该输出:
+---------+
| class |
+---------+
| Math |
+---------+
Note:
学生在每个课中不应被重复计算。
思路:从courses表中查找class字段,按照课程名称来分组,count统计出现个数,distinct去除重复行
select class
from courses
group by class
having count(distinct student) >= 5
2.leetcode(181)
Employee
表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee
表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
思路:将a和b进行内连接,查找a中的姓名,条件为a中的ManagerId和b中id相等的 且 a中的salary大于b中salary
/*
1:on条件先于where条件的执行。on条件是在生成临时表时候执行,where是在临时表生成后对数据进行筛选的。
2:先抛开where条件,看On条件。不管on条件是否成了,左表的所有行数据都会select出来(不管右表条件是否成立),右表成立就select对应数据,不成立就置null。有可能是一对多。
*/
select a.Name as Employee
from Employee a inner join Employee b on a.ManagerId = b.Id and a.Salary > b.Salary
3.leetcode(176)
编写一个 SQL 查询,获取 Employee
表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee
表,SQL查询应该返回 200
作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null
。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
思路:
使用 “IFNULL” 函数,IFNULL(expr1,expr2),含义是:如果第一个参数不为空,则返回第一个参数,否则返回第二个参数。
distinct去除重复后,从Employee查找中salary,按照salary降序排序,limit限制行数返回行数,offset 1表示从表的第1行开始
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
4.leetcode(182)
编写一个 SQL 查询,查找 Person
表中所有重复的电子邮箱。
示例:
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明:所有电子邮箱都是小写字母。
思路:关键点为having count的使用
select Email
from Person
group by Email
having count(Email) > 1;
5.leetcode(627)
给定一个 salary
表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。
注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句。
例如:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的更新语句之后,将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
思路:
通过case ....when ,当sex为m赋值为f,否则赋值为m
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;