SQL力扣练习(八)

这篇具有很好参考价值的文章主要介绍了SQL力扣练习(八)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

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窗口函数,思路与方法一类似

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模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • SQL Server 数据库之SQL Server 数据库的安全设置

    数据库服务器是所有应用的数据中转站,若数据库服务被恶意攻击,可能会造成数据泄露、数据丢失、数据被恶意篡改等诸多无法挽回的损失; 所以,对数据库进行安全设置是每一个数据库管理人员都应掌握的知识; SQL Server 登录模式分为 “Windows 身份验证模式” 和 “SQL

    2024年02月06日
    浏览(80)
  • SQL力扣练习(四)

    表: Employee   编写一个SQL查询来查找收入比经理高的员工。   示例 1: 在这里普及一下连接小知识。 1.​Left join:即左连接,是以左表为基础,根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出,而右表只列出ON后条件与左表满足的部分。左连接全

    2024年02月12日
    浏览(31)
  • SQL力扣练习(十一)

    目录 1.树节点(608) 示例 1 解法一(case when) 解法二(not in) 2.判断三角形(610) 示例 1 解法一(case when) 解法二(if) 解法三(嵌套if) 3.只出现一次的最大数字(619) 示例 1 解法一(count limit) 解法二(max) 4.有趣的电影(620) 解法一 5.换座位(626) 示例 1 解法一(case when) 解法二(count mod case-when) 解法三

    2024年02月12日
    浏览(35)
  • SQL力扣练习(九)

    目录 1.订单最多的用户(586) 示例 1 解法一(limit) 解法二(dense_rank()) 2.体育馆的人流量 示例 1 解法一(临时表) 解法二(三表法) 表:  Orders 查找下了  最多订单  的客户的  customer_number  。 测试用例生成后,  恰好有一个客户  比任何其他客户下了更多的订单。 查询结果格式如

    2024年02月14日
    浏览(35)
  • SQL力扣练习(六)

    目录 1. 部门工资前三高的所有员工(185) 题解一(dense_rank()窗口函数) 题解二(自定义函数)  2.删除重复的电子邮箱(196) 题解一 题解二(官方解析) 3.上升的温度(197) 解法一(DATEDIFF()) 解法二(TIMESTAMPDIFF()) 解法三(ADDDATE()力扣) 表:  Employee 表:  Department 公司的主管们感

    2024年02月13日
    浏览(32)
  • SQL力扣练习(八)

    目录 1.至少有五名直接下属的经理(570) 方法一(in) 方法二(join) 方法三(row_number) 方法四(自连接) 2.员工奖金(577) 方法一(left join) 3.寻找用户推荐人(584) 方法一 4.2016年的投资(585) 示例 方法一(join,count) 方法二(in) 方法四(连接加 in) 表:  Employee 查询 至少有5名直接下属 的经理   。

    2024年02月15日
    浏览(32)
  • SQL力扣练习(七)

    目录 1.行程和用户(262) 方法一(Left Join) 方法二(NOT IN) 方法三(Join) 2.游戏玩法分析I(511) 方法一(min) 3.游戏玩法分析I(550) 方法一(AVG) 表: Trips 表: Users 取消率  的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数

    2024年02月15日
    浏览(33)
  • SQL力扣练习(五)

    目录  1.从不订购的客户(183) 解法一(not in) 解法二(is) 解法三(not exists) 解法四(isnull函数) 2.部门工资最高的员工(184) 解法一(in) 解法二(rank窗口函数) 某网站包含两个表, Customers  表和  Orders  表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。 Customers  表:

    2024年02月12日
    浏览(35)
  • SQL力扣练习(十)

    目录 1.体育馆的人流量(501) 示例 1 解法一(row_number()) 解法二(自定义变量) 解法三 2.好友申请(602) 示例 解法一(union all) 解法二 3.销售员(607) 示例 解法一 解法二 表: Stadium 编写一个 SQL 查询以找出每行的人数大于或等于  100  且  id  连续的三行或更多行记录

    2024年02月14日
    浏览(36)
  • SQL Server数据库使用SQL Server代理实现数据库自动备份

    在现实中,为了保证数据的安全和完整,防止人为错误和硬件故障等造成的数据丢失和损坏,就需要用到数据库的备份,不同的数据库方法有所差别,我这边主讲SQL Server数据库的备份和使用SQL Server代理作业实现数据库的定时备份。 目录 一、开启SQL Server代理 1、找到SQL Serv

    2024年02月09日
    浏览(83)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包