目录
1.至少有五名直接下属的经理(570)
方法一(in)
方法二(join)
方法三(row_number)
方法四(自连接)
2.员工奖金(577)
方法一(left join)
3.寻找用户推荐人(584)
方法一
4.2016年的投资(585)
示例
方法一(join,count)
方法二(in)
方法四(连接加 in)
1.至少有五名直接下属的经理(570)
表: Employee
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | department | varchar | | managerId | int | +-------------+---------+ 在 SQL 中,id 是该表的主键列。 该表的每一行都表示雇员的名字、他们的部门和他们的经理的id。 如果managerId为空,则该员工没有经理。 没有员工会成为自己的管理者。
查询至少有5名直接下属的经理 。
以 任意顺序 返回结果表。
方法一(in)
直接先找出至少五个相同上司的id,然后in一下.
# Write your MySQL query statement below
select name from employee where id in
(select managerId from employee
group by managerId having count(*)>=5 )
方法二(join)
先找出满足的经理id,再join连接有一下,相比方法一较快.
# Write your MySQL query statement below
select name from employee e join
(select managerId from employee
group by managerId having count(*)>=5) temp
on e.id=temp.managerId
方法三(row_number)
用row_number给相同上司的排序,然后筛选出上司id,然后再自连接.方法较慢,不推荐.
# # Write your MySQL query statement below
select name from employee a join
(select managerId from
(select managerId,(ROW_NUMBER() over (partition by managerId order by id)) num
from employee ) e
group by e.managerId having max(e.num)>=5)
b on a.id=b.managerId
方法四(自连接)
自连接,思路很好,推荐方法二与该方法.
SELECT
e1. NAME
FROM
Employee AS e1
JOIN Employee AS e2 ON (e1.id = e2.managerid)
GROUP BY
e1.id
HAVING
count(e1.id) >= 5
2.员工奖金(577)
选出所有 bonus < 1000 的员工的 name 及其 bonus。
Employee
表单
+-------+--------+-----------+--------+ | empId | name | supervisor| salary | +-------+--------+-----------+--------+ | 1 | John | 3 | 1000 | | 2 | Dan | 3 | 2000 | | 3 | Brad | null | 4000 | | 4 | Thomas | 3 | 4000 | +-------+--------+-----------+--------+ empId 是这张表单的主关键字
Bonus
表单
+-------+-------+ | empId | bonus | +-------+-------+ | 2 | 500 | | 4 | 2000 | +-------+-------+ empId 是这张表单的主关键字
输出示例:
+-------+-------+ | name | bonus | +-------+-------+ | John | null | | Dan | 500 | | Brad | null | +-------+-------+
方法一(left join)
这里需要注意的就是bonus可以为空.
# Write your MySQL query statement below
select e.name,b.bonus
from employee e left join bonus b
on b.empId=e.empId
where b.bonus<1000 or b.bonus is null
3.寻找用户推荐人(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 | +------+
方法一
较简单,不再描述
# Write your MySQL query statement below
select name from customer
where referee_id != 2 or referee_id is null
4.2016年的投资(585)
Insurance
表:
+-------------+-------+ | Column Name | Type | +-------------+-------+ | pid | int | | tiv_2015 | float | | tiv_2016 | float | | lat | float | | lon | float | +-------------+-------+ pid 是这张表的主键。 表中的每一行都包含一条保险信息,其中: pid 是投保人的投保编号。 tiv_2015 是该投保人在 2015 年的总投保金额,tiv_2016 是该投保人在 2016 年的总投保金额。 lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。 lon 是投保人所在城市的经度。题目数据确保 lon 不为空。
请你编写一个 SQL 查询,报告 2016 年 (tiv_2016
) 所有满足下述条件的投保人的投保金额之和:
- 他在 2015 年的投保额 (
tiv_2015
) 至少跟一个其他投保人在 2015 年的投保额相同。 - 他所在的城市必须与其他投保人都不同(也就是说 (
lat, lon
) 不能跟其他任何一个投保人完全相同)。
tiv_2016
四舍五入的 两位小数 。
查询结果格式如下例所示。
示例
输入: Insurance 表: +-----+----------+----------+-----+-----+ | pid | tiv_2015 | tiv_2016 | lat | lon | +-----+----------+----------+-----+-----+ | 1 | 10 | 5 | 10 | 10 | | 2 | 20 | 20 | 20 | 20 | | 3 | 10 | 30 | 20 | 20 | | 4 | 10 | 40 | 40 | 40 | +-----+----------+----------+-----+-----+ 输出: +----------+ | tiv_2016 | +----------+ | 45.00 | +----------+ 解释: 表中的第一条记录和最后一条记录都满足两个条件。 tiv_2015 值为 10 与第三条和第四条记录相同,且其位置是唯一的。 第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同,并且位置与第三条记录相同,这也导致了第三条记录不符合题目要求。 因此,结果是第一条记录和最后一条记录的 tiv_2016 之和,即 45 。
方法一(join,count)
先分别查询条件一和条件二,然后再连接查询.
条件一:
select tiv_2015 from insurance group by tiv_2015 having count(*)>=2
条件二:
select * from insurance group by lat,lon having count(*)=1
# Write your MySQL query statement below
select round(sum(a.tiv_2016),2) tiv_2016 from
# 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同
(select * from insurance group by lat,lon having count(*)=1) a join
# 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
(select tiv_2015 from insurance group by tiv_2015 having count(*)>=2) b
on a.tiv_2015=b.tiv_2015
方法二(in)
concat()函数用于将两个字符串连接起来,形成一个单一的字符串
select round(sum(tiv_2016), 2) tiv_2016 from insurance
where tiv_2015 in (
select tiv_2015 from insurance group by tiv_2015 having count(*) > 1
) and concat(lat, lon) in (
select concat(lat, lon) from insurance group by lat, lon having count(*) = 1
);
方法三(count)
用count窗口函数,思路与方法一类似文章来源:https://www.toymoban.com/news/detail-615513.html
select round(sum(tiv_2016), 2) tiv_2016 from (
select
tiv_2016,
count(*) over(partition by tiv_2015) count_tiv_2015,
count(*) over(partition by lat, lon) count_lat_lon
from insurance
) as temp where count_lat_lon = 1 and count_tiv_2015 > 1
方法四(连接加 in)
先用自连接满足条件一,再用not in满足条件二文章来源地址https://www.toymoban.com/news/detail-615513.html
# Write your MySQL query statement below
select round(sum(distinct o1.TIV_2016), 2) tiv_2016
from insurance o1 inner join insurance o2
on o1.PID != o2.PID and o1.TIV_2015 = o2.TIV_2015
where (o1.LAT,o1.lon) not in
(select lat, lon from insurance where o1.PID != PID)
到了这里,关于SQL力扣练习(八)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!