2020.11.2
1
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
根据以上输入,你的查询应返回以下结果:
±--------+
| Email |
±--------+
| a@b.com |
±--------+
- 构建临时表得到每个email的总数,再筛选
select Email from(select Email,count(Email) as num from Person group by Email)as static where num>1;
- 使用group by和having
select Email from Person group by Email having count(Email)>1;
2
给定一个 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 |
update slaray set sex=
CASE sex
WHEN 'm' then 'f'
else 'm'
END;
2020.11.3
620、有趣的电影
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。
例如,下表 cinema:
±--------±----------±-------------±----------+
| id | movie | description | rating |
±--------±----------±-------------±----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
±--------±----------±-------------±----------+
对于上面的例子,则正确的输出是为:
±--------±----------±-------------±----------+
| id | movie | description | rating |
±--------±----------±-------------±----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
±--------±----------±-------------±----------+
select id,movie,description,rating from cinema
where id%2=1 and description!='boring' order by rating desc;
改进 mod取余、not like 判断
select id,movie,description,rating from cinema
where mod(id,2)=1 and description not like 'boring' order by rating desc;
175、组合两个表
表1: Person
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键
表2: Address
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。 在使用left jion时,on和where条件的区别如下:
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
left join 没有匹配也会保存左表的数据
select FirstName,LastName,City,State from Person left join
Address on Person.PersonId=Address.PersonId;
2020.11.4
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 |
±---------+
- 自己的思路 308ms
select e1.name as Employee
from Employee as e1 join Employee as e2 on e1.ManagerId=e2.Id
where e1.Salary>e2.Salary;
- 题解一(直接笛卡尔积) 401ms
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
- 题解二 (子查询)
select a.Name as Employee
from Employee a, (select Salary,Id from Employee) b
where a.ManagerId=b.Id and a.Salary > b.Salary
183、从不订购的客户
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers 表:
±—±------+
| Id | Name |
±—±------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
±—±------+
Orders 表:
±—±-----------+
| Id | CustomerId |
±—±-----------+
| 1 | 3 |
| 2 | 1 |
±—±-----------+
例如给定上述表格,你的查询应返回:
±----------+
| Customers |
±----------+
| Henry |
| Max |
±----------+
- 自己的思路
因为要取的是没有购买的客户,所以要取id!=customers的,但是因为使用多表查询所以使用不等于的条件会出现很多干扰项,所以用左连接找到所有id=customers的项后剩下的customers为null的项就是没有购买的客户
select Name as Customers
from Customers left join Orders on Customers.Id=CustomerId
where CustomerId is null;
- 题解一 使用 not in
select customers.name as 'Customers'
from customers
where customers.id not in
(
select customerid from orders
);
2020.11.5
196.删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
±—±-----------------+
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:
±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
±—±-----------------+
提示:
执行 SQL 之后,输出是整个 Person 表。
使用 delete 语句。
delete p1 from Person p1,Person p2 where p1.Email=p2.Email AND p1.Id>p2.Id;
delete p1 表示从p1表中删除满足where条件的记录
1179、重新格式化部门表
部门表 Department:
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| revenue | int |
| month | varchar |
±--------------±--------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份(month)可以取下列值 [“Jan”,“Feb”,“Mar”,“Apr”,“May”,“Jun”,“Jul”,“Aug”,“Sep”,“Oct”,“Nov”,“Dec”]。
编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。
查询结果格式如下面的示例所示:
Department 表:
±-----±--------±------+
| id | revenue | month |
±-----±--------±------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
±-----±--------±------+
查询得到的结果表:
±-----±------------±------------±------------±----±------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue |
±-----±------------±------------±------------±----±------------+
| 1 | 8000 | 7000 | 6000 | … | null |
| 2 | 9000 | null | null | … | null |
| 3 | null | 10000 | null | … | null |
±-----±------------±------------±------------±----±------------+
注意,结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。
聚集函数配合group by使用
SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE `month` WHEN 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE `month` WHEN 'May' THEN revenue END) May_Revenue,
SUM(CASE `month` WHEN 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE `month` WHEN 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE `month` WHEN 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE `month` WHEN 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE `month` WHEN 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE `month` WHEN 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE `month` WHEN 'Dec' THEN revenue END) Dec_Revenue
FROM Department
GROUP BY id;
197、上升的温度
表 Weather
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| recordDate | date |
| temperature | int |
±--------------±--------+
id 是这个表的主键
该表包含特定日期的温度信息
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例:
Weather
±—±-----------±------------+
| id | recordDate | Temperature |
±—±-----------±------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
±—±-----------±------------+
Result table:
±—+
| id |
±—+
| 2 |
| 4 |
±—+
2015-01-02 的温度比前一天高(10 -> 25)
2015-01-04 的温度比前一天高(30 -> 20)
DATEDIFF用来比较两个日期类型的值
select w1.id AS 'Id'
from Weather w1 join Weather w2
on DATEDIFF(w1.recorddate,w2.recorddate)=1 AND w1.Temperature >w2.Temperature;
2020.11.6
596.超过5名学生的课
有一个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 |
±--------+
提示:
学生在每个课中不应被重复计算。
group by+having
distinct 去重
select class
from courses
group by class having count(distinct student)>4;
group by+子查询
子查询表需要定义名称
select class
from(
select class,count(distinct student)as num
from courses group by class)as temp
where num>4;