排版好有颜色的文章在公众号“八哥的成长心路札记”上有，微信号是bager1912。
以下题目均来自力扣（LeetCode）官网和其他网站，仅用作数据库爱好者学习交流，严禁进行商业及任何非法用途。
603. 连续空余座位
几个朋友来到电影院的售票处，准备预约连续空余座位。
你能利用表cinema，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？
| seat_id | free |
|---------|------|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
对于如上样例，你的查询语句应该返回如下结果。
| seat_id |
|---------|
| 3 |
| 4 |
| 5 |
注意：
seat_id 字段是一个自增的整数，free 字段是布尔类型（'1' 表示空余， '0' 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。
selectdistincta.seat_id
fromcinema a,cinema b
where(a.seat_id-1=b.seat_idora.seat_id+1=b.seat_id)and(a.free=1andb.free=1)
orderbya.seat_id;
607. 销售员
描述
给定 3 个表：salesperson，company，orders。
输出所有表salesperson中，没有向公司 'RED' 销售任何东西的销售员。
解释
输入
表：salesperson
+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
| 1 | John | 100000 | 6 | 4/1/2006 |
| 2 | Amy | 120000 | 5 | 5/1/2010 |
| 3 | Mark | 65000 | 12 | 12/25/2008|
| 4 | Pam | 25000 | 25 | 1/1/2005 |
| 5 | Alex | 50000 | 10 | 2/3/2007 |
+----------+------+--------+-----------------+-----------+
表salesperson存储了所有销售员的信息。每个销售员都有一个销售员编号sales_id和他的名字name 。
表：company
+---------+--------+------------+
| com_id | name | city |
+---------+--------+------------+
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
+---------+--------+------------+
表company存储了所有公司的信息。每个公司都有一个公司编号com_id和它的名字name。
表：orders
+----------+------------+---------+----------+--------+
| order_id | order_date | com_id | sales_id | amount |
+----------+------------+---------+----------+--------+
| 1 | 1/1/2014 | 3 | 4 | 100000 |
| 2 | 2/1/2014 | 4 | 5 | 5000 |
| 3 | 3/1/2014 | 1 | 1 | 50000 |
| 4 | 4/1/2014 | 1 | 4 | 25000 |
+----------+----------+---------+----------+--------+
表orders存储了所有的销售数据，包括销售员编号sales_id 和公司编号com_id。
输出
+------+
| name |
+------+
| Amy |
| Mark |
| Alex |
+------+
解释
根据表orders中的订单 '3' 和 '4' ，容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。
所以我们需要输出表salesperson中所有其他人的名字。
selectname
fromsalesperson
wheresales_idnotin(selectsales_id
fromorders ojoincompany c
ono.com_id=c.com_id
wherec.name='RED');
608. 树节点
给定一个表tree，id是树节点的编号，p_id是它父节点的id 。
+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
树中每个节点属于以下三种类型之一：
叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。
写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
+----+------+
| id | Type |
+----+------+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+
解释
节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3' 。
节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。
节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。
样例中树的形态如下：
1
/ \
2 3
/ \
4 5
注意
如果树中只有一个节点，你只需要输出它的根属性。
selectid,'Root'Type
fromtree
wherep_idisnull
union
selectid,'Inner'Type
fromtree
whereidin(selectdistinctp_id
fromtree
wherep_idisnotnull)andp_idisnotnull
union
selectid,'Leaf'Type
fromtree
whereidnotin(selectdistinctp_id
fromtree
wherep_idisnotnull)andp_idisnotnull
orderbyid;
610. 判断三角形
一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。
然而，这个作业非常繁重，因为有几百组线段需要判断。
假设表table保存了所有三条线段的三元组 x, y, z ，你能帮 Tim 写一个查询语句，来判断每个三元组是否可以组成一个三角形吗？
| x | y | z |
|----|----|----|
| 13 | 15 | 30 |
| 10 | 20 | 15 |
对于如上样例数据，你的查询语句应该返回如下结果：
| x | y | z | triangle |
|----|----|----|----------|
| 13 | 15 | 30 | No |
| 10 | 20 | 15 | Yes |
selecttriangle.*,(casewhenx+y>zandx+z>yandy+z>xthen'Yes'else'No'end)"triangle"
fromtriangle;
这个LeetCode让我有点郁闷，把表名字命名为table，也是无语了，看了评论说是表名为triangle。
612. 平面上的最近距离
表point_2d保存了所有点（多于 2 个点）的坐标 (x,y) ，这些点在平面上两两不重合。
写一个查询语句找到两点之间的最近距离，保留 2 位小数。
| x | y |
|----|----|
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
最近距离在点 (-1,-1) 和(-1,2) 之间，距离为 1.00 。所以输出应该为：
| shortest |
|----------|
| 1.00 |
注意：任意点之间的最远距离小于 10000 。
selectround(SQRT(min(power((p1.x-p2.x),2)+power((p1.y-p2.y),2))),2)"shortest"
frompoint_2d p1joinpoint_2d p2
onp1.x!=p2.xorp1.y!=p2.y;
613. 直线上的最近距离
表point保存了一些点在 x 轴上的坐标，这些坐标都是整数。
写一个查询语句，找到这些点中最近两个点之间的距离。
| x |
|-----|
| -1 |
| 0 |
| 2 |
最近距离显然是 '1' ，是点 '-1' 和 '0' 之间的距离。所以输出应该如下：
| shortest|
|---------|
| 1 |
注意：每个点都与其他点坐标不同，表table不会有重复坐标出现。
进阶：如果这些点在 x 轴上从左到右都有一个编号，输出结果时需要输出最近点对的编号呢？
selectmin(abs(p1.x-p2.x))"shortest"
frompoint p1,point p2
wherep1.x!=p2.x;
614. 二级关注者
在 facebook 中，表follow会有 2 个字段：followee,follower，分别表示被关注者和关注者。
请写一个 sql 查询语句，对每一个关注者，查询他的关注者数目。
比方说：
+-------------+------------+
| followee | follower |
+-------------+------------+
| A | B |
| B | C |
| B | D |
| D | E |
+-------------+------------+
应该输出：
+-------------+------------+
| follower | num |
+-------------+------------+
| B | 2 |
| D | 1 |
+-------------+------------+
解释：
B 和 D 都在在follower字段中出现，作为被关注者，B 被 C 和 D 关注，D 被 E 关注。A 不在follower字段内，所以A不在输出列表中。
注意：
被关注者永远不会被他 / 她自己关注。
将结果按照字典序返回。
selectdistinctf2.follower,f1.num
from(selectfollowee,nvl(count(distinctcn),0)num
from
(selectfollowee,concat(followee,follower) cn
fromfollow)
groupbyfollowee) f1joinfollow f2
onf1.followee=f2.follower
orderbyf2.follower;
这道题怎么改提交都不通过，很奇怪，不知道哪个地方有问题，而相似的mysql版本的却能通过，知道的朋友麻烦告诉我下，谢谢？
SELECTDISTINCTf2.follower,f1.num
FROM(SELECTfollowee,IFNULL(COUNT(DISTINCTfollowee,follower),0)ASnum
FROMfollow
GROUPBYfollowee) f1
INNERJOINfollow f2
ONf1.followee = f2.follower
ORDERBYf2.follower
;
615. 平均工资：部门与公司比较
给如下两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）。
表：salary
| id | employee_id | amount | pay_date |
|----|-------------|--------|------------|
| 1 | 1 | 9000 | 2017-03-31 |
| 2 | 2 | 6000 | 2017-03-31 |
| 3 | 3 | 10000 | 2017-03-31 |
| 4 | 1 | 7000 | 2017-02-28 |
| 5 | 2 | 6000 | 2017-02-28 |
| 6 | 3 | 8000 | 2017-02-28 |
employee_id字段是表employee中employee_id字段的外键。
| employee_id | department_id |
|-------------|---------------|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
对于如上样例数据，结果为：
| pay_month | department_id | comparison |
|-----------|---------------|-------------|
| 2017-03 | 1 | higher |
| 2017-03 | 2 | lower |
| 2017-02 | 1 | same |
| 2017-02 | 2 | same |
解释
在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33...
由于部门 '1' 里只有一个employee_id为 '1' 的员工，所以部门 '1' 的平均工资就是此人的工资 9000 。因为 9000 > 8333.33 ，所以比较结果是 'higher'。
第二个部门的平均工资为employee_id为 '2' 和 '3' 两个人的平均工资，为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ，所以比较结果是 'lower' 。
在二月用同样的公式求平均工资并比较，比较结果为 'same' ，因为部门 '1' 和部门 '2' 的平均工资与公司的平均工资相同，都是 7000 。
selectdt"pay_month",department_id"department_id",casewhenbv>avthen'higher'
whenbv=avthen'same'
else'lower'end"comparison"
from(selectdt,department_id,av,avg(amount) bv
from(selectto_char(pay_date,'YYYY-MM') dt,avg(amount) av
fromsalary
groupbyto_char(pay_date,'YYYY-MM')) s,salary s1,employee e
wheres1.employee_id=e.employee_idands.dt=to_char(s1.pay_date,'YYYY-MM')
groupbydt,department_id,av)
orderbydepartment_id,dt;
这个地方注意下分组，group by to_char(pay_date,'YYYY-MM')，而不能是group by pay_date。
618. 学生地理信息报告
一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下student表中。
| name | continent |
|--------|-----------|
| Jack | America |
| Pascal | Europe |
| Xi | Asia |
| Jane | America |
写一个查询语句实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生。
对于样例输入，它的对应输出是：
| America | Asia | Europe |
|---------|------|--------|
| Jack | Xi | Pascal |
| Jane | | |
进阶：如果不能确定哪个大洲的学生数最多，你可以写出一个查询去生成上述学生报告吗？
selecta"America",b"Asia",c"Europe"
from(selectrn,min(casecontinentwhen'America'thennameelse''end) a ,
min(casecontinentwhen'Asia'thennameelse''end) b ,
min(casecontinentwhen'Europe'thennameelse''end) c
from(selectstudent.*,row_number()over(partitionbycontinentorderbyname) rn
fromstudent)
groupbyrn)
orderbya,b,c;
想了蛮久，竟然被我想出来了，不容易。这个min好像换成max也可以的。因为排序函数已经按字母顺序排好序了。但是最外面一层还需要加个order by，因为对于continent内部是排好序了，但是外面对于同一层，比如都是2，都是3的那一层并没有排序。
既然做到了行列互换，那就再记录下今天做到的另一题，算是用sql进行多行多列相互转换，以此记录：
导进plsql developer里如图所示：
经过一番折腾，终于变换成功：
八哥觉得看着不爽，一定要按照既定要求显示，于是改改代码：
现在是对了，让我们来显示中文看看：
这么看着很完美，八哥略有点点成就感，如果需要源代码的朋友欢迎点赞，转发，或留言(#^.^#)没有的话，八哥就私藏了哈
619. 只出现一次的最大数字
表my_numbers的num字段包含很多数字，其中包括很多重复的数字。
你能写一个 SQL 查询语句，找到只出现过一次的数字中，最大的一个数字吗？
+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
对于上面给出的样例数据，你的查询语句应该返回如下结果：
+---+
|num|
+---+
| 6 |
注意：
如果没有只出现一次的数字，输出null。
selectmax(num)"num"
frommy_numbers
wherenumin(selectnum
frommy_numbers
groupbynum
havingcount(*)=1);
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*
fromcinema
whereid%2=1anddescription!='boring'
orderbyratingdesc;
626. 换座位
小美是一所中学的信息科技老师，她有一张seat座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
SELECT(CASE
WHENMOD(id,2) =1ANDid= (SELECTCOUNT(*)FROMseat)THENid
WHENMOD(id,2) =1THENid+1
ElSEid-1
END)ASid, student
FROMseat
ORDERBYid;
摘自blacksheep，这个思路太好了。
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 |
updatesalary
setsex=casesexwhen'm'then'f'
else'm'end;
感觉这道题应该叫交换性别，有木有。。。
1045. Customers Who Bought All Products
Table:Customer
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
product_key is a foreign key toProducttable.
Table:Product
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_key | int |
+-------------+---------+
product_key is the primary key column for this table.
Write an SQL query for a report that provides the customer ids from theCustomertable that bought all the products in theProducttable.
For example:
Customer table:
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1 | 5 |
| 2 | 6 |
| 3 | 5 |
| 3 | 6 |
| 1 | 6 |
+-------------+-------------+
Product table:
+-------------+
| product_key |
+-------------+
| 5 |
| 6 |
+-------------+
Result table:
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
The customers who bought all the products (5 and 6) are customers with id 1 and 3.
selectdistinctcustomer_id
fromcustomer
whereproduct_keyin(selectproduct_key
fromproduct)
groupbycustomer_id
havingcount(*)=(selectcount(*)
fromproduct);
这个做法在LeetCode里死活无法通过，试了好几种类似的写法，均提交失败，哪位大神更懂的给指指路，谢谢！
1050. Actors and Directors Who Cooperated At Least Three Times
able:ActorDirector
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp is the primary key column for this table.
Write a SQL query for a report that provides the pairs(actor_id, director_id)where the actor have cooperated with the director at least 3 times.
Example:
ActorDirector table:
+-------------+-------------+-------------+
| actor_id | director_id | timestamp |
+-------------+-------------+-------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+-------------+-------------+
Result table:
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
The only pair is (1, 1) where they cooperated exactly 3 times.
selectactor_id,director_id
from(selectactor_id,director_id,count(*) cn
fromactordirector
groupbyactor_id,director_id)
wherecn>=3;
至此，截止2019年05月28日，LeetCode数据库全部44题，已全部解析完毕，鉴于个人能力有限，难免会有些许差错，欢迎各位看官批评指正，提出新的想法或建议，八哥与你共同学习，一起成长！
本专题完结！
网友评论