SQL学习总目标
第一阶段:leetcode的所有免费题跟着答案抄一遍- 第二阶段:不看答案的情况下写3~4遍,理解题目和实现逻辑
- 第三阶段:换用Oracle与SQL server写一遍
- 第四阶段:做思维导图进行知识总结
Leetcode的SQL入门题
第一天
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 |
+-----------+
写法一:条件+子查询
SELECT Name AS Customers
From customers
WHERE Id NOT IN(
SELECT CustomerId FROM Orders
)
584. 寻找用户推荐人
给定表 customer
,里面保存了所有客户信息和他们的推荐人。
+------+------+-----------+
| id | name | referee_id|
+------+------+-----------+
| 1 | Will | NULL |
| 2 | Jane | NULL |
| 3 | Alex | 2 |
| 4 | Bill | NULL |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
+------+------+-----------+
写一个查询语句,返回一个客户列表,列表中客户的推荐人的编号都 不是 2。
对于上面的示例数据,结果为:
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+
写法一:条件
空单独判断
SELECT name
FROM customer
WHERE referee_id is NULL or referee_id!=2
1757. 可回收且低脂的产品
表:Products
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| low_fats | enum |
| recyclable | enum |
+-------------+---------+
product_id 是这个表的主键。
low_fats 是枚举类型,取值为以下两种 ('Y', 'N'),其中 'Y' 表示该产品是低脂产品,'N' 表示不是低脂产品。
recyclable 是枚举类型,取值为以下两种 ('Y', 'N'),其中 'Y' 表示该产品可回收,而 'N' 表示不可回收。
写出 SQL 语句,查找既是低脂又是可回收的产品编号。
返回结果 无顺序要求 。
查询结果格式如下例所示:
Products 表:
+-------------+----------+------------+
| product_id | low_fats | recyclable |
+-------------+----------+------------+
| 0 | Y | N |
| 1 | Y | Y |
| 2 | N | Y |
| 3 | Y | Y |
| 4 | N | N |
+-------------+----------+------------+
Result 表:
+-------------+
| product_id |
+-------------+
| 1 |
| 3 |
+-------------+
只有产品 id 为 1 和 3 的产品,既是低脂又是可回收的产品。
写法一:AND条件
SELECT product_id
FROM Products
WHERE low_fats='Y' and recyclable='Y';
595. 大的国家
World
表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | int |
+-------------+---------+
name 是这张表的主键。
这张表的每一行提供:国家名称、所属大陆、面积、人口和 GDP 值。
如果一个国家满足下述两个条件之一,则认为该国是 大国 :
- 面积至少为 300 万平方公里(即,
3000000 km2
),或者 - 人口至少为 2500 万(即
25000000
)
编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。
按 任意顺序 返回结果表。
查询结果格式如下例所示。
示例:
输入:
World 表:
+-------------+-----------+---------+------------+--------------+
| name | continent | area | population | gdp |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
+-------------+-----------+---------+------------+--------------+
输出:
+-------------+------------+---------+
| name | population | area |
+-------------+------------+---------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+-------------+------------+---------+
写法一:OR条件
SELECT name,population,area
FROM World
WHERE population>=25000000 OR area>=3000000;
知识点(其实主要考条件):
- 联合查询UNION是不是跟或相近?
- 条件:与、或关系
- 三种判断类型:真、假、空,其中空是用等号判断嘛?
第二天
196. 删除重复的电子邮箱
表: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。
以 任意顺序 返回结果表。 (注意: 仅需要写删除语句,将自动对剩余结果进行查询)
查询结果格式如下所示。
示例 1:
输入:
Person 表:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example |
| 2 | bob@example |
| 3 | john@example |
+----+------------------+
输出:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example |
| 2 | bob@example |
+----+------------------+
解释: john@example重复两次。我们保留最小的Id = 1。
写法一:子嵌入
DELETE FROM Person
WHERE id not in(
SELECT * FROM(#取中间表
SELECT min(id)
FROM Person
group by email
) AS t
);
错误:
在MYSQL中,如果选择用镶嵌的条件,则不能先Select一个表的记录,再按此条件Update和Delete同一个表的记录,否则会出错:You can’t specify target table ‘xxx’ for update in FROM clause. 所以要使用嵌套Select——将Select得到的查询结果作为中间表(记得取别名),再Select一遍中间表作为结果集,即可规避错误。另外,请注意本题题意,提示要用Delete删除重复项。
知识点:
- 在有条件判断的情况下,删除单表的某些信息,其实可以利用表的别名来进行判断
- 如果使用了表的别名,则在删除的情况下,要写成
delete 表n的别名 from 表1,表2....... where 条件;
如果不是则是写成delete from 表
。
写法二:条件+自表连接
同一个表取两个别名,然后条件表明两个表的email是相等的就可以做到类似group by的效果,并且只要令其中一个表p1小于另一个表p2就可以做到类似min的效果。
DELETE p1
FROM Person p1 ,Person p2
WHERE p1.email=p2.email AND p1.id > p2.id
627. 变更性别
Salary
表:
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型,只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。
请你编写一个 SQL 查询来交换所有的 'f'
和 'm'
(即,将所有 'f'
变为 'm'
,反之亦然),仅使用 单个 update 语句 ,且不产生中间临时表。
注意,你必须仅使用一条 update 语句,且 不能 使用 select 语句。
查询结果如下例所示。
示例 1:
输入:
Salary 表:
+----+------+-----+--------+
| 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 |
+----+------+-----+--------+
解释:
(1, A) 和 (3, C) 从 'm' 变为 'f' 。
(2, B) 和 (4, D) 从 'f' 变为 'm' 。
知识点:
update 表 set 字段根据条件变成XX
与delete from 表
可是要注意from- 多条件表达的输出结果 可用子镶嵌
case 字段 when 条件1 then 输出1 else 输出2 end
1873. 计算特殊奖金
表: Employees
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| employee_id | int |
| name | varchar |
| salary | int |
+-------------+---------+
employee_id 是这个表的主键。
此表的每一行给出了雇员id ,名字和薪水。
写出一个SQL 查询语句,计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头,那么他的奖金是他工资的100%,否则奖金为0。
Return the result table ordered by employee_id
.
返回的结果集请按照employee_id
排序。
查询结果格式如下面的例子所示。
示例 1:
输入:
Employees 表:
+-------------+---------+--------+
| employee_id | name | salary |
+-------------+---------+--------+
| 2 | Meir | 3000 |
| 3 | Michael | 3800 |
| 7 | Addilyn | 7400 |
| 8 | Juan | 6100 |
| 9 | Kannon | 7700 |
+-------------+---------+--------+
输出:
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2 | 0 |
| 3 | 0 |
| 7 | 7400 |
| 8 | 0 |
| 9 | 7700 |
+-------------+-------+
解释:
因为雇员id是偶数,所以雇员id 是2和8的两个雇员得到的奖金是0。
雇员id为3的因为他的名字以'M'开头,所以,奖金是0。
其他的雇员得到了百分之百的奖金。
知识点:
- 多条件表达的输出结果其实 也可以写成
(case when 条件 then 输出1 else 输出2 end) as 别名
来创建一个新的字段,当然不是只有case when才能创建新的字段,实际上所有的函数都是可以的,只不过确实要 写出别名才行,另外注意:这条语句里面输出如果不是故意设定某一个值,而是想要设定字段原本的值,则用该字段来表示即可。 name rlike '^m'
意思是找到以m开头的name
字段内容,也可以替换成left(name,1)='M'
- MOD(字段,数值)=?
写法一:case when end
SELECT employee_id,
(CASE
WHEN MOD(employee_id,2)=1 AND name NOT rlike '^M' THEN salary ELSE 0
END) AS bonus
FROM Employees
ORDER BY employee_id;
写法二: if
SELECT employee_id,
(if(employee_id %2 = 1 and left(name,1)!='M', salary, 0)) AS bonus
FROM Employees
ORDER BY employee_id
第三天
1527. 患某种疾病的患者
患者信息表: Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id (患者 ID)是该表的主键。
'conditions' (疾病)包含 0 个或以上的疾病代码,以空格分隔。
这个表包含医院中患者的信息。
写一条 SQL 语句,查询患有 I 类糖尿病的患者 ID (patient_id)、患者姓名(patient_name)以及其患有的所有疾病代码(conditions)。I 类糖尿病的代码总是包含前缀 DIAB1
。
按 任意顺序 返回结果表。
查询结果格式如下示例所示。
示例 1:
输入:
Patients表:
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 1 | Daniel | YFEV COUGH |
| 2 | Alice | |
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
| 5 | Alain | DIAB201 |
+------------+--------------+--------------+
输出:
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
+------------+--------------+--------------+
解释:Bob 和 George 都患有代码以 DIAB1 开头的疾病。
写法一:REGEXP(类似rlike正则表达式)
SELECT * FROM PATIENTS
WHERE CONDITIONS REGEXP '^DIAB1|\\sDIAB1'
- 因为不同的疾病使用一个空格分开,所以患有I型糖尿病的患者的conditions有两种情况
- 糖尿病位于第一个时: 以
DIAB1
开始,即CONDITIONS REGEXP '^DIAB1
- 糖尿病不是第一个时: 含有
空格DIAB1
,即CONDITIONS REGEXP '\\sDIAB1
,其中’\s’表示空格.
写法二:
select * from Patients where conditions rlike '^DIAB1|.*\\sDIAB1';
^DIAB1表示以DIAB1开头
|表示或者
.表示一定有任意一个字符
*表示重复0到无穷多个的前一个字符
第一个\表示转义字符
\s是指空白,包括空格、换行、Tab 缩进等所有的空白
所以.*\sDIAB1
表示DIAB1前是空格且空格前有0到无穷多个的任意字符
知识点:
- RLIKE : 模糊匹配,只要字段的值中存在要查找的 部分 就会被选择出来,正则的写法与java一样。‘‘需要使用’’,例如’\w’需要使用双斜杠
- LIKE :全字段匹配,字段 like ‘%m’ 的意思是字段找到以m为结尾的字段内容,%代表任意多个字符。
1484. 按日期分组销售产品
表 Activities
:
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
此表没有主键,它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date
排序的结果表。
查询结果格式如下例所示。
示例 1:
输入:
Activities 表:
+------------+-------------+
| sell_date | product |
+------------+-------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
+------------+-------------+
输出:
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
解释:
对于2020-05-30,出售的物品是 (Headphone, Basketball, T-shirt),按词典序排列,并用逗号 ',' 分隔。
对于2020-06-01,出售的物品是 (Pencil, Bible),按词典序排列,并用逗号分隔。
对于2020-06-02,出售的物品是 (Mask),只需返回该物品名。
写法一:
SELECT sell_date,
COUNT(distinct product) AS num_sold,
group_concat(distinct product order by product,',') products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date
CONCAT(),GROUP_CONCAT()有什么不同?
知识点:
concat(str1, str2,...)
,返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null,比如select concat (id, name, score) as info from tt2;
中间有一行为null是因为tt2表中有一行的score值为null。GROUP_CONCAT(expr)
函数会从 expr字段 中连接所有非 NULL 的字符串。如果没有非 NULL 的字符串,那么它就会返回 NULL。该函数多用于递归,需要注意需不需要去重distinct 字段
。
1667. 修复表中的名字
表: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| name | varchar |
+----------------+---------+
user_id 是该表的主键。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。
编写一个 SQL 查询来修复名字,使得只有第一个字符是大写的,其余都是小写的。
返回按 user_id
排序的结果表。
查询结果格式示例如下。
示例 1:
输入:
Users table:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | aLice |
| 2 | bOB |
+---------+-------+
输出:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | Alice |
| 2 | Bob |
+---------+-------+
写法一:
SELECT
user_id,
concat(upper(left(name,1)),lower(substr(name,2))) as name
FROM
Users
order by
user_id
这题是用left
函数取名字首字符转为大写,用substr
函数取名字第二个及之后所有字符转为小写,再拼接即可。
知识点:
substr(string,pos,end)
这个函数不填入end就是取pos位置及其之后所有的字符。left(string,pos,end)
也是同样的写法- upper(字段),lower(字段)就如表面意思
第四天
176. 第二高的薪水 - 力扣(LeetCode) (leetcode-cn)
其实这题可以用子镶嵌和max()函数来实现,不过这样就太low了,如果这题是使用分页查询的话需要注意以下知识点。
知识点:
- 判断临界输出,如果不存在第二高的薪水,查询应返回 null,使用
ifNull(查询,null)
函数,当然其实使用外层select语句就可以了,还真没必要 select ifNull()语句。 - 在查询的去重、接着排序的语句后,可以使用
limit()
方法,查询第二大的数据,即第二高的薪水,即 limit(1,1) (因为默认从0开始,所以第一个1是查询第二大的数,第二个1是表示往后显示多少条数据,这里只需要一条) IFNULL(查询,值)
意思是如果查询出位空就设定为某个值
608. 树节点 - 力扣(LeetCode) (leetcode-cn)
知识点:
- 多判断查询语句的写法是
when 条件1 then 输出1 when 条件2 输出2 else 输出3
1795. 每个产品在不同商店的价格 - 力扣(LeetCode) (leetcode-cn)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0AHfqC5K-1676870403557)(C:\Users\LENOVO\AppData\Roaming\Typora\typora-user-images\image-20220510090441399.png)]
这题其实本质上就算多列转单行,列转行用union all
。
如果以这题为例子行转列用groupby product_id + 查询中sum(if(store='该字段内容',price,0))
,具体怎么写自己清楚。
知识点:
- 一般都是会想到
字段 别名
这种写法,但是其实'XX' 别名
也是同一个意思 - union对两个结果集进行并集操作,不包括重复行,相当于distinct,同时进行默认规则的排序;
- union all:对两个结果集进行并集操作,包括重复行,即所有的结果全部显示,不管是不是重复。
1965. 丢失信息的雇员 - 力扣(LeetCode) (leetcode-cn)
这题其实可以借用联合查询UNION再加个正常的条件判断就行了,不过这样写其实没多秀,我们换成聚合的知识点如下
知识点:
- 中间表查询要起别名
- group by 后面可接having 条件,而coun()函数其实除了平常可以统计的作用外,其实还可以这样理解,两个表我要查找出有空缺的,那么是不是主键id不可能统计出有两个以上。
第五天
1148. 文章浏览 I - 力扣(LeetCode)
这题没什么特别的,也就注意去重
1581. 进店却未进行过交易的顾客 - 力扣(LeetCode)
子镶嵌+group by+not in判断
或
连接+group by+空判断
175. 组合两个表 - 力扣(LeetCode)
左连接
第六天
607. 销售员 - 力扣(LeetCode)
子镶嵌+左连接+not in
197. 上升的温度 - 力扣(LeetCode)
左连接
知识点:
datediff(日期1, 日期2)
: 得到的结果是日期1与日期2相差的天数。 如果日期1比日期2大,结果为正;如果日期1比日期2小,结果为负。
核心思路:
连接:SELECT * FROM A LEFT OUTER JOIN B ON A.id=B.id WHERE sth
其中WHERE是赛选查询的条件,而ON后面接的是连接的条件
第七天
查询近30天活跃用户数 - 查询近30天活跃用户数 - 力扣(LeetCode)
1693. 每天的领导和合伙人 - 力扣(LeetCode)
1729. 求关注者的数量 - 力扣(LeetCode)
核心思路:
聚合+条件
第八天
586. 订单最多的客户 - 力扣(LeetCode) (leetcode-cn)
这题只用注意同一天可能有同一个用户,其他没什么特别之处
511. 游戏玩法分析 I - 力扣(LeetCode) (leetcode-cn)
这题提示第一次登陆平台的日期,所以要用min函数聚合,当然如果最后一次则用max()
1890. 2020年最后一次登录 - 力扣(LeetCode) (leetcode-cn)
Year()函数
1741. 查找每个员工花费的总时间 - 力扣(LeetCode) (leetcode-cn)
计算每位员工每天在办公室花费的总时间(以分钟为单位),则用sum()函数进行不同字段之间的加减乘除,而如果是同一个字段则需要结合case when、if()、聚合 等条件判断进行输出了,毕竟有的题目不可能直接让你全都要吧。
注意:sum是可以返回空的
收获:
- 原来data类型的日期也是可以比较大小用min()函数
- 其实这些计算函数来来去去就那几个,只不过要配合GROUP BY +WHERE条件之类的
第九天
1158. 市场分析 I - 力扣(LeetCode) (leetcode-cn)
外连接时要注意where和on的区别,on是在连接构造临时表时执行的,不管on中条件是否成立都会返回主表(也就是left join左边的表)的内容,where是在临时表形成后执行筛选作用的,不满足条件的整行都会被过滤掉。如果这里用的是 where year(order_date)='2019'
那么得到的结果将会把不满足条件的user_id为3,4的行给删掉。用on的话会保留user_id为3,4的行。
1407. 排名靠前的旅行者 - 力扣(LeetCode) (leetcode-cn)
排序的知识看看廖雪峰老师的教程就清楚了
1393. 股票的资本损益 - 力扣(LeetCode) (leetcode-cn)
第十天
182. 查找重复的电子邮箱 - 力扣(LeetCode) (leetcode-cn)
1050. 合作过至少三次的演员和导演 - 力扣(LeetCode) (leetcode-cn)
1587. 银行账户概要 II - 力扣(LeetCode) (leetcode-cn)
1084. 销售分析III - 力扣(LeetCode)
SELECT p.product_id,p.product_name FROM Product p,Sales s WHERE p.product_id=s.product_id GROUP BY s.product_id HAVING MIN(sale_date)>='2019-01-01' AND MAX(sale_date)<='2019-03-31';
这题也可以用连接把两个表合起来再进行聚合、having判断
select p.product_id, p.product_name from Sales s left join Product p on s.product_id = p.product_id group by s.product_id having MIN(s.sale_date) >= '2019-01-01' and MAX(s.sale_date) <= '2019-03-31'
知识点:
- 聚合group by+having赛选聚合的条件
剩下的免费题
177. 第N高的薪水
表: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。
编写一个SQL查询来报告 Employee
表中第 n
高的工资。如果没有第 n
个最高工资,查询应该报告为 null
。
查询结果格式如下所示。
示例 1:
输入:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
输出:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
示例 2:
输入:
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
n = 2
输出:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null |
+------------------------+
注意:
set
不要放到begin
里面去- 可能存在重复的
salary
- 排序要倒序,
LIMIT
应该要限制的位置是哪里?
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
RETURN (
\# Write your MySQL query statement below.
SELECT DISTINCT salary FROM Employee
ORDER BY salary DESC LIMIT N,1);
END
178. 分数排名
表: Scores
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| score | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。
编写 SQL 查询对分数进行排序。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等,那么两个分数的排名应该相同。
- 在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按 score
降序返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
输出:
+-------+------+
| score | rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
思路:
- 从两张相同的表scores分别命名为s1,s2。
- s1中的score与s2中的score比较大小。意思是在输出s1.score的前提下,有多少个s2.score大于等于它。比如当s1.salary=3.65的时候,s2.salary中
[4.00,4.00,3.85,3.65,3.65]
有5个成绩大于等于他,但是利用count(distinct s2.score)
去重可得s1.salary3.65的rank为3 。 group by s1.id
不然的话只会有一条数据- 最后根据
s1.score
排序desc
SELECT s1.score score,count(distinct s2.Score) as 'rank'
FROM Scores s1,Scores s2
WHERE s1.score<=s2.score
GROUP BY s1.id
ORDER BY s1.score DESC;
180. 连续出现的数字
表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示:
示例 1:
输入:
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
输出:
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解释:1 是唯一连续出现至少三次的数字。
写法一:自链接
这题我暂时没想到什么好的方法,只能创建三个同一个表,直接比较
SELECT DISTINCT a.Num as ConsecutiveNums
FROM Logs as a,Logs as b,Logs as c
WHERE a.Num=b.Num and b.Num=c.Num and a.id=b.id-1 and b.id=c.id-1;
181. 超过经理收入的员工
表:Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
Id是该表的主键。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。
编写一个SQL查询来查找收入比经理高的员工。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Employee 表:
+----+-------+--------+-----------+
| id | name | salary | managerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
+----+-------+--------+-----------+
输出:
+----------+
| Employee |
+----------+
| Joe |
+----------+
解释: Joe 是唯一挣得比经理多的雇员。
写法一:自链接
两表连接,其中managerId
的意思是当前这个雇员的经理,所以先用条件组合起来,再比较工资的大小即可
SELECT e2.name as Employee
FROM Employee e1,Employee e2
WHERE e1.id=e2.managerId
AND e1.salary<e2.salary
184. 部门工资最高的员工
表: Employee
+--------------+---------+
| 列名 | 类型 |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。
表: Department
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。
编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。
示例 1:
输入:
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
输出:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
解释:Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。
写法一:自链接与子查询
- 自链接的作用是为了把两个表根据部门id合起来;
- 子查询的目的是根据部门的聚合查找出最高工资
SELECT d.name as Department,e.name as Employee,e.salary as Salary
FROM Employee e,Department d
WHERE e.departmentId=d.id
AND (e.salary,e.departmentId) in (SELECT max(salary),departmentId FROM Employee
GROUP BY departmentId)
185. 部门工资前三高的所有员工
表: Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
Id是该表的主键列。
departmentId是Department表中ID的外键。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
Id是该表的主键列。
该表的每一行表示部门ID和部门名。
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写一个SQL查询,找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
输出:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释:
在IT部门:
- Max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的
在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资,因为只有两名员工
错误案例
这题用子查询聚合分页limit+外部内连接处理(防止空值出现)是存在问题的,因为
如果sql语句中的子查询包含limit
例如: select * from a where id in (select id from b limit 3)
会报错:This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ #
解决办法:
1、加一层子查询
例如:select * from a where id in (select t.id from (select id from b limit 3 )as t)
2、把限制条件放到from而非where子句中,就不必出现嵌套再嵌套。
例如:select * from (select id from a limit 3) as foo
所以就算弄了个中间表,其实它最终识别的意思是全部只找到三个,而不是找到3个以内的各部门最高工资者。
SELECT d.name AS Department,e.name AS Employee,e.salary AS Salary
FROM Employee e
INNER JOIN Department d ON e.departmentId=d.id
WHERE (e.departmentId,e.salary) IN (
SELECT * FROM(
SELECT departmentId,salary
FROM Employee
GROUP BY departmentId
ORDER BY salary DESC
LIMIT 3
)AS t
)
结果:
{"headers": ["Department", "Employee", "Salary"],
"values": [["IT", "Joe", 85000],
["Sales", "Henry", 80000],
["IT", "Randy", 85000]]}
正确写法一:使用 JOIN
和子查询但不需要limit
公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大(注意这里有个隐含条件是它们都是不同一个部门)
select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
select count(distinct e2.Salary)
from Employee e2
where e2.Salary > e1.Salary
)
在这个代码里,我们统计了有多少人的工资比 e1.Salary 高,所以样例的输出应该如下所示。
| Employee | Salary |
|----------|--------|
| Henry | 80000 |
| Max | 90000 |
| Randy | 85000 |
然后,我们需要把表 Employee 和表 Department 连接来获得部门信息。
SELECT d.Name AS 'Department',e1.name AS 'Employee',e1.salary AS 'Salary'
FROM Employee e1
# 部门id和部门名称要连起来
INNER JOIN
Department d ON e1.departmentId=d.id
WHERE 3>(
SELECT COUNT(distinct e2.salary)
FROM Employee e2
WHERE e2.salary > e1.salary
# 注意是比较的是同一个部门
AND e1.departmentId=e2.departmentId
)
262. 行程和用户
表:Trips
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | date |
+-------------+----------+
id 是这张表的主键。
这张表中存所有出租车的行程信息。每段行程有唯一 id ,其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。
status 是一个表示行程状态的枚举类型,枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表:Users
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| users_id | int |
| banned | enum |
| role | enum |
+-------------+----------+
users_id 是这张表的主键。
这张表中存所有用户,每个用户都有一个唯一的 users_id ,role 是一个表示用户身份的枚举类型,枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型,枚举成员为 (‘Yes’, ‘No’) 。
取消率 的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
写一段 SQL 语句查出 "2013-10-01"
至 "2013-10-03"
期间非禁止用户(乘客和司机都必须未被禁止)的取消率。非禁止用户即 banned 为 No 的用户,禁止用户即 banned 为 Yes 的用户。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate
需要四舍五入保留 两位小数 。
查询结果格式如下例所示。
示例:
输入:
Trips 表:
+----+-----------+-----------+---------+---------------------+------------+
| id | client_id | driver_id | city_id | status | request_at |
+----+-----------+-----------+---------+---------------------+------------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
+----+-----------+-----------+---------+---------------------+------------+
Users 表:
+----------+--------+--------+
| users_id | banned | role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
输出:
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
解释:
2013-10-01:
- 共有 4 条请求,其中 2 条取消。
- 然而,id=2 的请求是由禁止用户(user_id=2)发出的,所以计算时应当忽略它。
- 因此,总共有 3 条非禁止请求参与计算,其中 1 条取消。
- 取消率为 (1 / 3) = 0.33
2013-10-02:
- 共有 3 条请求,其中 0 条取消。
- 然而,id=6 的请求是由禁止用户发出的,所以计算时应当忽略它。
- 因此,总共有 2 条非禁止请求参与计算,其中 0 条取消。
- 取消率为 (0 / 2) = 0.00
2013-10-03:
- 共有 3 条请求,其中 1 条取消。
- 然而,id=8 的请求是由禁止用户发出的,所以计算时应当忽略它。
- 因此,总共有 2 条非禁止请求参与计算,其中 1 条取消。
- 取消率为 (1 / 2) = 0.50
#对Trips表和Users表连接,连接条件是行程对应的乘客非禁止且司机非禁止
#筛选订单日期在目标日期之间(BETWEEN AND)
#用日期进行分组(GROUP BY)
#分别统计所有订单数和被取消的订单数,其中取消订单数用一个bool条件来得到0或1,再用avg求均值
#对订单取消率保留两位小数,对输出列名改名。(round)
SELECT
request_at 'Day',round(avg(status!='completed'),2) 'Cancellation Rate'
FROM Trips t
JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No')
JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No')
WHERE
request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY
request_at;
注意:Join后面的字段开头大写,不知道为什么小写就报错了
596. 超过5名学生的课
表: Courses
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| student | varchar |
| class | varchar |
+-------------+---------+
(student, class)是该表的主键列。
该表的每一行表示学生的名字和他们注册的班级。
编写一个SQL查询来报告 至少有5个学生 的所有类。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Courses table:
+---------+----------+
| student | class |
+---------+----------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+----------+
输出:
+---------+
| class |
+---------+
| Math |
+---------+
解释:
-数学课有6个学生,所以我们包括它。
-英语课有1名学生,所以我们不包括它。
-生物课有1名学生,所以我们不包括它。
-计算机课有1个学生,所以我们不包括它。
写法一:使用 GROUP BY
和 HAVING
条件
SELECT
class
FROM
courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;
学生有可能重修,所以要加distinct,直接写count(class)也能达到同样的效果
写法二:使用 GROUP BY
子句和子查询
先使用 GROUP BY
和 COUNT
获得每门课程的学生数量。
SELECT
class, COUNT(DISTINCT student)
FROM
courses
GROUP BY class;
得到下表
| class | COUNT(student) |
|----------|----------------|
| Biology | 1 |
| Computer | 1 |
| English | 1 |
| Math | 6 |
由于COUNT(student)
不能直接在 WHERE
子句中使用,所以要将其重命名为 num
,而这个num
位于中间表t
中,WHERE条件则写在外层查询的判断中。
SELECT class
FROM (SELECT class,
COUNT(DISTINCT student) AS num
FROM Courses
GROUP BY class) AS t
WHERE num>=5;
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 MOD(id,2)=1 AND description!='boring'
ORDER BY rating DESC;
601. 体育馆的人流量
表:Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录,日期随着 id 的增加而增加
编写一个 SQL 查询以找出每行的人数大于或等于 100
且 id
连续的三行或更多行记录。
返回按 visit_date
升序排列 的结果表。
查询结果格式如下所示。
示例 1:
输入:
Stadium 表:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
输出:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
解释:
id 为 5、6、7、8 的四行 id 连续,并且每行都有 >= 100 的人数记录。
请注意,即使第 7 行和第 8 行的 visit_date 不是连续的,输出也应当包含第 8 行,因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行,因为至少需要三条 id 连续的记录。
写法一:通过条件来写出id的排列
SELECT distinct a.* #由于是用排列,所以会有可能重复
FROM stadium a,stadium b,stadium c
WHERE a.people>=100 and b.people>=100 and c.people>=100
AND (#下面是条件排列
(a.id = b.id-1 and b.id = c.id -1) or (a.id = b.id-1 and a.id = c.id +1) or (a.id = b.id+1 and b.id = c.id +1)
)
order by a.id
626. 换座位
表: Seat
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
Id是该表的主键列。
该表的每一行都表示学生的姓名和ID。
Id是一个连续的增量。
编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数,则最后一个学生的id不交换。
按 id
升序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Seat 表:
+----+---------+
| id | student |
+----+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+----+---------+
输出:
+----+---------+
| id | student |
+----+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+----+---------+
解释:
请注意,如果学生人数为奇数,则不需要更换最后一名学生的座位。
写法一:自连接
# Write your MySQL query statement below
# 首先创建一个三表自连接,这三个表分别代表着id,id+1,id-1,那么肯定会遇到有些列为空
# 然后首先要判断是奇偶,再判断是否为空来选择对应的表即可
# 其实下面这句通过具体的案例会更加好理解:
# ifnull(if(MOD(a.id,2)=0,b.student,c.student),a.student) as student
SELECT a.id as id,
ifnull(if(MOD(a.id,2)=0,b.student,c.student),a.student) as student
FROM seat a
LEFT OUTER JOIN seat b ON a.id=b.id+1
LEFT OUTER JOIN seat c ON a.id=c.id-1
ORDER BY a.id ASC;
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个月份的收入列)。
写法一:多条件判断+聚合函数SUM()
# Write your MySQL query statement below
SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
SUM(CASE `month` WHEN 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
SUM(CASE `month` WHEN 'May' THEN revenue ELSE NULL END) AS May_Revenue,
SUM(CASE `month` WHEN 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
SUM(CASE `month` WHEN 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
SUM(CASE `month` WHEN 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
SUM(CASE `month` WHEN 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
SUM(CASE `month` WHEN 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
SUM(CASE `month` WHEN 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
SUM(CASE `month` WHEN 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM Department
group by id;
更多推荐
【SQL个人刷题笔记】MySQL版
发布评论