力扣SQL仅数据库（580-608）

580. 统计各专业学生人数

需求：

编写解决方案，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的  字母顺序 排序。

数据准备：

Create table If Not Exists Student (student_id int,student_name varchar(45), gender varchar(6), dept_id int)
Create table If Not Exists Department (dept_id int, dept_name varchar(255))
Truncate table Student
insert into Student (student_id, student_name, gender, dept_id) values ('1', 'Jack', 'M', '1')
insert into Student (student_id, student_name, gender, dept_id) values ('2', 'Jane', 'F', '1')
insert into Student (student_id, student_name, gender, dept_id) values ('3', 'Mark', 'M', '2')
Truncate table Department
insert into Department (dept_id, dept_name) values ('1', 'Engineering')
insert into Department (dept_id, dept_name) values ('2', 'Science')
insert into Department (dept_id, dept_name) values ('3', 'Law')

代码实现：

select d.dept_name,count(student_id) student_number
        from student s right join department d on s.dept_id=d.dept_id
        group by d.dept_id,d.dept_name
        order by student_number desc, dept_name;

584. 寻找用户推荐人

需求：找出那些 没有被 id = 2 的客户 推荐 的客户的姓名。

数据准备：

Create table If Not Exists Customer (id int, name varchar(25), referee_id int)
Truncate table Customer
insert into Customer (id, name, referee_id) values ('1', 'Will', 'None')
insert into Customer (id, name, referee_id) values ('2', 'Jane', 'None')
insert into Customer (id, name, referee_id) values ('3', 'Alex', '2')
insert into Customer (id, name, referee_id) values ('4', 'Bill', 'None')
insert into Customer (id, name, referee_id) values ('5', 'Zack', '1')
insert into Customer (id, name, referee_id) values ('6', 'Mark', '2')

代码实现：

select name from customer where referee_id !=2 or referee_id is null;

585. 2016年的投资

需求：编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：

• 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
• 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。

tiv_2016 四舍五入的 两位小数 。

数据准备：

Create Table If Not Exists Insurance (pid int, tiv_2015 float, tiv_2016 float, lat float, lon float)
Truncate table Insurance
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('1', '10', '5', '10', '10')
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('2', '20', '20', '20', '20')
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('3', '10', '30', '20', '20')
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('4', '10', '40', '40', '40')

代码实现：

t 筛选出在2015年的投保额与任何一个其他投保人的投保额相同的

tt 筛选出城市（lat,lon）与任何一个其他投保人都不相同的

with t as ( select pid,tiv_2016,concat(lat,lon) conn from insurance where tiv_2015 in
        (select tiv_2015 from insurance group by tiv_2015 having count(tiv_2015)>1)),
 tt as (select tiv_2016,concat(lat,lon) con from insurance)
select round(sum(tiv_2016),2) tiv_2016 from t where conn in  (select con from tt group by con having count(con)=1) ;

586. 订单最多的客户

需求：查找下了 最多订单 的客户的 customer_number 。

测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

数据准备：

Create table If Not Exists orders (order_number int, customer_number int)
Truncate table orders
insert into orders (order_number, customer_number) values ('1', '1')
insert into orders (order_number, customer_number) values ('2', '2')
insert into orders (order_number, customer_number) values ('3', '3')
insert into orders (order_number, customer_number) values ('4', '3')

代码实现：

使用窗口函数

with t as (select customer_number,count(order_number)over(partition by customer_number) con from orders)
select distinct customer_number from t order by con desc limit 1;

不使用窗口函数：

select customer_number from orders group by customer_number order by count(order_number) desc limit 1;

595. 大的国家

需求：编写解决方案找出 大国 的国家名称、人口和面积。

如果一个国家满足下述两个条件之一，则认为该国是 大国 ：

• 面积至少为 300 万平方公里（即，3000000 km2），或者
• 人口至少为 2500 万（即 25000000）

数据准备：

Create table If Not Exists World (name varchar(255), continent varchar(255), area int, population int, gdp bigint)
Truncate table World
insert into World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', '652230', '25500100', '20343000000')
insert into World (name, continent, area, population, gdp) values ('Albania', 'Europe', '28748', '2831741', '12960000000')
insert into World (name, continent, area, population, gdp) values ('Algeria', 'Africa', '2381741', '37100000', '188681000000')
insert into World (name, continent, area, population, gdp) values ('Andorra', 'Europe', '468', '78115', '3712000000')
insert into World (name, continent, area, population, gdp) values ('Angola', 'Africa', '1246700', '20609294', '100990000000')

代码实现：

select name,World.population,World.area from world where area>=3000000 or population>=25000000;

596. 超过5名学生的课

需求：查询 至少有 5 个学生 的所有班级。

数据准备：

Create table If Not Exists Courses (student varchar(255), class varchar(255))
Truncate table Courses
insert into Courses (student, class) values ('A', 'Math')
insert into Courses (student, class) values ('B', 'English')
insert into Courses (student, class) values ('C', 'Math')
insert into Courses (student, class) values ('D', 'Biology')
insert into Courses (student, class) values ('E', 'Math')
insert into Courses (student, class) values ('F', 'Computer')
insert into Courses (student, class) values ('G', 'Math')
insert into Courses (student, class) values ('H', 'Math')
insert into Courses (student, class) values ('I', 'Math')

代码实现：

select class from courses group by class having count(student)>=5;

601. 体育馆的人流量

需求：编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列 的结果表。

数据准备：

Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int)
Truncate table Stadium
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10')
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109')
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150')
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99')
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145')
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '1455')
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199')
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', '188')

代码实现：

t 将人数>=100的筛选出来

tt 对数据使用窗口函数增加一列排序列

ttt 跟据 id号-序号可看出相等时说明序号是连续的，跟据该原理对数据分组统计，筛选出>=3的值

with t as (select * from stadium where people>=100),
tt as ( select *,rank()over(order by id) ran from t),
ttt as (select *,id-ran cha,count(id-ran)over(partition by id-ran) cou from tt)
select id,visit_date,people from ttt where cou>=3 order by visit_date;

597. 好友申请1

需求：求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

• 通过的好友申请不一定都在表 friend_request 中。你只需要统计总的被通过的申请数（不管它们在不在表 FriendRequest 中），并将它除以申请总数，得到通过率
• 一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
• 如果一个好友申请都没有，你应该返回 accept_rate 为 0.00 。

数据准备：

Create table If Not Exists FriendRequest (sender_id int, send_to_id int, request_date date)
Create table If Not Exists RequestAccepted (requester_id int, accepter_id int, accept_date date)
Truncate table FriendRequest
insert into FriendRequest (sender_id, send_to_id, request_date) values ('1', '2', '2016/06/01')
insert into FriendRequest (sender_id, send_to_id, request_date) values ('1', '3', '2016/06/01')
insert into FriendRequest (sender_id, send_to_id, request_date) values ('1', '4', '2016/06/01')
insert into FriendRequest (sender_id, send_to_id, request_date) values ('2', '3', '2016/06/02')
insert into FriendRequest (sender_id, send_to_id, request_date) values ('3', '4', '2016/06/09')
Truncate table RequestAccepted
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '2', '2016/06/03')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '3', '2016/06/08')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('2', '3', '2016/06/08')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('3', '4', '2016/06/09')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('3', '4', '2016/06/10')

代码实现：

with t1 as (select count(distinct concat(sender_id,send_to_id))con1  from friendrequest)
,t2 as (select count(distinct concat(requester_id,accepter_id))con2  from requestaccepted)
select ifnull(round(con2/con1,2),0)accept_rate from t1,t2;

602 好友申请2

需求：编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。

生成的测试用例保证拥有最多好友数目的只有 1 个人。

数据准备：

Create table If Not Exists RequestAccepted (requester_id int not null, accepter_id int null, accept_date date null)
Truncate table RequestAccepted
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '2', '2016/06/03')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '3', '2016/06/08')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('2', '3', '2016/06/08')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('3', '4', '2016/06/09')

代码实现：

好友是相互的，因此不会有（1，2）与（2，1）的情况，所以直接对两列进行合并，筛选出 id 出现的次数最多的

with t1 as (select requester_id as id from requestaccepted union all select accepter_id as id from requestaccepted)
select id,count(id) num from t1 group by id order by num desc limit 1;

603 连续空余座位

需求：查找电影院所有连续可用的座位。

返回按 seat_id 升序排序 的结果表。

测试用例的生成使得两个以上的座位连续可用。

数据准备：

Create table If Not Exists Cinema (seat_id int primary key auto_increment, free bool)
Truncate table Cinema
insert into Cinema (seat_id, free) values ('1', '1')
insert into Cinema (seat_id, free) values ('2', '0')
insert into Cinema (seat_id, free) values ('3', '1')
insert into Cinema (seat_id, free) values ('4', '1')
insert into Cinema (seat_id, free) values ('5', '1')

代码实现：

空余的连续两个及以上座位

with t1 as (select *,lag(free,1) over(order by seat_id)free1,lead(free,1) over(order by seat_id)free2   from cinema)
select seat_id from t1 where free != 0 and (free=free1 or free=free2);

拓展：空余的连续三个及以上座位

with t1 as (select *,lag(free,1) over(order by seat_id)lag1
                    ,lag(free,2) over(order by seat_id)lag2
                    ,lead(free,1) over(order by seat_id)lead1
                    ,lead(free,2) over(order by seat_id)lead2
            from cinema)
select seat_id from t1 where free=1 and (free=lead1 and free=lead2)
        or (free=lag1 and free=lag2)or (free=lead1 and free=lag1);

607 销售员

需求：编写解决方案，找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名

数据准备：

Create table If Not Exists SalesPerson (sales_id int, name varchar(255), salary int, commission_rate int, hire_date date)
Create table If Not Exists Company (com_id int, name varchar(255), city varchar(255))
Create table If Not Exists Orders (order_id int, order_date date, com_id int, sales_id int, amount int)
Truncate table SalesPerson
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('1', 'John', '100000', '6', '4/1/2006')
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('2', 'Amy', '12000', '5', '5/1/2010')
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('3', 'Mark', '65000', '12', '12/25/2008')
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('4', 'Pam', '25000', '25', '1/1/2005')
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('5', 'Alex', '5000', '10', '2/3/2007')
Truncate table Company
insert into Company (com_id, name, city) values ('1', 'RED', 'Boston')
insert into Company (com_id, name, city) values ('2', 'ORANGE', 'New York')
insert into Company (com_id, name, city) values ('3', 'YELLOW', 'Boston')
insert into Company (com_id, name, city) values ('4', 'GREEN', 'Austin')
Truncate table Orders
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('1', '1/1/2014', '3', '4', '10000')
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('2', '2/1/2014', '4', '5', '5000')
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('3', '3/1/2014', '1', '1', '50000')
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('4', '4/1/2014', '1', '4', '25000')

代码准备：

with a as (select Orders.sales_id,name from orders left join company  on Orders.com_id=Company.com_id where name='RED')
select name  from salesperson where sales_id not in (select sales_id from a)

 608 树节点

需求：编写一个解决方案来报告树中每个节点的类型

树中的每个节点可以是以下三种类型之一：

• "Leaf"：节点是叶子节点。
• "Root"：节点是树的根节点。
• "lnner"：节点既不是叶子节点也不是根节点。

数据准备：

Create table If Not Exists Tree (id int, p_id int)
Truncate table Tree
insert into Tree (id, p_id) values ('1', 'None')
insert into Tree (id, p_id) values ('2', '1')
insert into Tree (id, p_id) values ('3', '1')
insert into Tree (id, p_id) values ('4', '2')
insert into Tree (id, p_id) values ('5', '2')

代码实现：

注意点：当一个节点没有父节点也没有子节点时，该节点也为根节点Root

with t1 as (select *,if(p_id is not null,1,0)fu_if,
       if(id in (select p_id from tree),1,0)zi_id
       from tree)
select id,
       case when fu_if =0 then 'Root'
               when fu_if !=0 and zi_id !=0 then 'Inner'
               when fu_if !=0 and zi_id =0 then 'Leaf'
        end type
from t1;