mysql over rank_MySQL实战

最新推荐文章于 2023-10-20 15:13:41 发布
最新推荐文章于 2023-10-20 15:13:41 发布
阅读量83 收藏
点赞数
文章标签： mysql over rank
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42557768/article/details/112503580
版权

cc41b38849e2b291bd7be4f0192744d0.png

经过前面三期的学习，相信大家对MySQL有了一定的了解，基础知识了解的差不多了，大家现在一定跃跃欲试，想看看自己到底掌握得怎样吧？那么，这一期就是MySQL项目了实战了。

一、数据导入/导出

参考：

MySql csv文件导入导出 - 那一剑的風情 - 博客园​www.cnblogs.com
2fe1a777c5ed88e7d7d9af3c66b88611.png

二、项目实战

项目七: 各部门工资最高的员工（难度：中等）

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+

| Id | Name | Salary | DepartmentId |

+----+-------+--------+--------------+

| 1 | Joe | 70000 | 1 |

| 2 | Henry | 80000 | 2 |

| 3 | Sam | 60000 | 2 |

| 4 | Max | 90000 | 1 |

+----+-------+--------+--------------+

创建Department 表，包含公司所有部门的信息。

+----+----------+

| Id | Name |

+----+----------+

| 1 | IT |

| 2 | Sales |

+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT | Max | 90000 |

| Sales | Henry | 80000 |

+------------+----------+--------+

参考：

#创建Department表
CREATE TABLE Department
(
Id INT PRIMARY KEY,
Name VARCHAR(20)
);

#创建Employee表
CREATE TABLE Employee
(
Id INT PRIMARY KEY,
Name VARCHAR(20),
Salary INT,
DepartmentId int,
CONSTRAINT f_fk FOREIGN KEY(DepartmentId) REFERENCES Department(Id)
);

#给Department表插入数据
INSERT INTO Department
VALUE(1, 'IT'), (2, 'Sales');

#给Employee表插入数据
INSERT INTO Employee
VALUES(1, 'Joe', 70000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam', 60000, 2),
(4, 'Max', 90000, 1);

#方法一：
SELECT e.Name Employee, d.Name Department, e.Salary
FROM (SELECT DepartmentId, MAX(Salary) Salary
	FROM Employee
	GROUP BY DepartmentId) m, Employee e, Department d
WHERE m.DepartmentId = e.DepartmentId
	AND m.Salary = e.Salary
    AND e.DepartmentId = d.Id
ORDER BY Salary DESC;

#方法二：
SELECT e.Name Employee, d.Name Department, e.Salary
FROM Employee e
JOIN Department d
ON e.DepartmentId = d.Id
WHERE (e.Salary, e.DepartmentId) IN(
	SELECT MAX(Salary) Salary, DepartmentId
	FROM Employee
    GROUP BY DepartmentId)
ORDER BY Salary DESC;

项目八: 换座位（难度：中等）

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

请创建如下所示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 |

+---------+---------+

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

参考：

#创建表seat
CREATE TABLE seat
(
id INT PRIMARY KEY AUTO_INCREMENT,
student VARCHAR(25)
);
#插入数据
INSERT INTO seat
VALUES(1, 'Abbot'), 
(2, 'Doris'), 
(3, 'Emerson'), 
(4, 'Green'), 
(5, 'Jeames');

#方法一：
SELECT (CASE
	WHEN MOD(id, 2) = 1 AND id != (SELECT MAX(id) FROM seat) THEN id + 1
    WHEN MOD(id, 2) = 0 THEN id- 1
    ELSE id
    END) id, student
FROM seat
ORDER BY id;

#方法二：
SELECT 
	IF(MOD(id, 2) = 1,
    IF(id != (SELECT MAX(id) FROM seat), id, id + 1), id - 1) id, student
FROM seat
ORDER BY id;

项目九: 分数排名（难度：中等）

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

创建以下score表：

+----+-------+

| Id | Score |

+----+-------+

| 1 | 3.50 |

| 2 | 3.65 |

| 3 | 4.00 |

| 4 | 3.85 |

| 5 | 4.00 |

| 6 | 3.65 |

+----+-------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+

| Score | Rank |

+-------+------+

| 4.00 | 1 |

| 4.00 | 1 |

| 3.85 | 2 |

| 3.65 | 3 |

| 3.65 | 3 |

| 3.50 | 4 |

+-------+------+

参考：

#创建表

CREATE TABLE score
(
Id INT PRIMARY KEY,
Score FLOAT
);

#插入数据
INSERT INTO score
VALUES(1, 3.50),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65);

#排名
SELECT Score, DENSE_RANK()OVER(ORDER BY Score DESC) Ranks
FROM score;

项目十：行程和用户（难度：困难）

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

+----+-----------+-----------+---------+--------------------+----------+

| 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 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

+----------+--------+--------+

| 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 |

+----------+--------+--------+

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

+------------+-------------------+

| Day | Cancellation Rate |

+------------+-------------------+

| 2013-10-01 | 0.33 |

| 2013-10-02 | 0.00 |

| 2013-10-03 | 0.50 |

+------------+-------------------+

参考：

#创建表Trips
CREATE TABLE If NOT EXISTS Trips 
(
Id INT, 
Client_Id INT, 
Driver_Id INT, 
City_Id INT, 
Status ENUM('completed','cancelled_by_driver', 'cancelled_by_client'), 
Request_at VARCHAR(50)
);
#创建表Users
CREATE TABLE If NOT EXISTS Users 
(
Users_Id INT, 
Banned VARCHAR(50), 
Role ENUM('client', 'driver', 'partner')
);
#插入数据
INSERT INTO Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) 
VALUES('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');


INSERT INTO Users (Users_Id, Banned, Role)
VALUES('1', 'No', 'client'),
('2', 'Yes', 'client'),
('3', 'No', 'client'),
('4', 'No', 'client'),
('10', 'No', 'driver'),
('11', 'No', 'driver'),
('12', 'No', 'driver'),
('13', 'No', 'driver');

#查询取消率
SELECT t.Request_at Day,
ROUND(SUM((CASE 
		WHEN t.Status LIKE 'cancelled%' THEN 1 
		ELSE 0 
	    END))/COUNT(*),2) 'Cancellation Rate'
FROM Trips t
JOIN Users u 
ON u.Users_Id =t.Client_Id AND u.Banned = 'No'
WHERE t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at;

项目十一：各部门前3高工资的员工（难度：中等）

将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）：

+----+-------+--------+--------------+

| Id | Name | Salary | DepartmentId |

+----+-------+--------+--------------+

| 1 | Joe | 70000 | 1 |

| 2 | Henry | 80000 | 2 |

| 3 | Sam | 60000 | 2 |

| 4 | Max | 90000 | 1 |

| 5 | Janet | 69000 | 1 |

| 6 | Randy | 85000 | 1 |

参考：

#清除employee表中数据
DELETE FROM employee;

#插入数据
INSERT INTO employee
VALUES(1, 'Joe', 70000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam', 60000, 2),
(4, 'Max', 90000, 1),
(5, 'Janet', 69000, 1),
(6, 'Randy', 85000, 1);

#查找每个部门工资前三高的员工
SELECT d.Name Department, e.Name Employee, e.Salary
FROM (
        SELECT Name, Salary, DepartmentId, 
		ROW_NUMBER()OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) r
        FROM Employee) e
JOIN Department d
ON e.DepartmentId = d.Id
WHERE e.r <= 3; #若考虑前几高的员工，只需要改变 e.r <= n就行了， n表示前几高;

项目十二 分数排名 - （难度：中等）

依然是昨天的分数表，实现排名功能，但是排名是非连续的，如下：

+-------+------+

| Score | Rank |

+-------+------+

| 4.00 | 1 |

| 4.00 | 1 |

| 3.85 | 3 |

| 3.65 | 4 |

| 3.65 | 4 |

| 3.50 | 6 |

+-------+------

参考：

#排名
SELECT Score, RANK()OVER(ORDER BY Score DESC) Ranks
FROM score;
陈丰Element Chan
关注
mysql dense_rank_DENSE_RANK分析函数的使用
weixin_34847632的博客
01-19 1149
突然发现DENSE_RANK是个不错的函数，以前一直以为FIRST_VALUE,LAST_VALUE可以替代 ,但是其实不然.有时候可以用的到大家。 DENSE_RANK 功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排突然发现DENSE_RANK是个不错的函数，以前一直以为FIRST_VALUE,LAST_VALU...
mysql rank函数_Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
weixin_35777914的博客
01-19 3132
排名函数是Sql Server2005新增的功能，下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。表结构和初始数据Sql附上表结构和初始数据图：一、ROW_NUMBERrow_number的用途的非常广泛，排序最好用他，一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必...
mysql over rank_sql - MySQL中的Rank函数
weixin_39588679的博客
12-24 223
sql - MySQL中的Rank函数我需要找出客户的排名。 在这里，我为我的要求添加了相应的ANSI标准SQL查询。 请帮我转换为MySQL。SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],FirstName,Age,GenderFROM Person有没有找到MySQL排名的函数？...
mysql over rank_mysql实现Oracle 的rank() over()函数
weixin_31081671的博客
12-24 219
之前要查询排序且有排名，如果是oracle的话有rank() over()函数，但是mysql没有这样的函数，只能自己试着用变量实现表结构：SELECT id,score ,@rank := @rank + 1 AS rankFROM a, (SELECT @rank := 0) tmpORDER BY score desc ；不过这样有一个问题如果同时有两个99分rank也会顺序的排序我想到...
【C++】友元的理解和使用
w903414的博客
10-25 387
先思考一个问题，如何将不属于当前结构的函数访问当前结构中的成员？ 在这里就引出了友元这个概念。在当前结构中声明结构外函数为友元函数，则该友元函数就可访问该结构中的所有成员。 友元又分为：友元函数和友元类。 目录1.友元函数1.1 如何对`<< 和 >>`进行重载呢？2.友元类 1.友元函数 友元函数可访问类中的所有成员，但不是该类中的成员函数。 例： class Date { friend void Print(Date& d);//友元函数 public: Date(i
mysql 排名_MySQL分类排名问题
weixin_39766910的博客
11-23 352
导读对数据库中的记录依据某个字段进行排序是一种常见需求，虽然简单的Order by可以胜任，但如果想要输出具体的排名却难以直接实现。如果再考虑重复排名或者分类排名，那么情况就更为复杂。本文介绍4种分类排名方式：子查询、自连接、自定义变量以及MySQL8.0窗口函数。01 需求介绍考虑MySQL中的一个经典应用：给定一个学生考试成绩表，要实现对学生按课程依成绩高低进行排序。为了简单起见，仅给定成绩表...
Hive个人笔记总结
zp的博客
12-15 973
Hive是一个数据仓库软件 Hive主要使用HQL（类sql）来分析已经存储在分布式设备(HDFS)上的数据 Hive的本质是将用户编写的HQL，转换为MR/spark程序，对数据进行分析 Hive分析的数据必须是结构化的数据，在分析之前，用户需要对数据创建表结构 Hive的表结构(shema)存储在关系型数据库中，数据是存储在HDFS上，二者通过表进行映射 Hive基于OLAP设计，基于OLAP设计的软件，一般重分析，延时高！
mysql8 中的rank_Mysql8.0+中的rank()、row_num()、dense_rank()等窗口函数
weixin_30311179的博客
02-05 925
在低于Mysql8.0之前的版本中，要对序列进行排序的话需要用到自定义参数@，但是8.0+版本中加入了oracle中的窗口函数，rank()、row_num()、dense_rank()等函数。下面简单举几个例子介绍下这几个函数的应用。测试用的表主要有两个，一个是学生信息表student，二是学生分数表sc，具体的测试数据的mysql插入语句在另一篇博客：Mysql经典练习题与知识点总结中，大家可...
低版本mysql 实现row_number() ,rank() ,dense_rank()及其窗口函数
A_single_cat的博客
11-24 1962
测试表 create table test (id int(4) , name VARCHAR(10) ) row_number() 实现 SELECT t.name , t.id, (@cur_rank := @cur_rank + 1) ranking FROM test t , (SELECT @cur_rank := 0) r ORDER BY id DESC; ROW_NUMBER() over(partiton by name ORDER BY id DESC) 实现
MYSQL学习笔记（4）——窗口函数2
A133333的博客
03-21 1221
题目复盘——窗口函数2 复盘两个难题 SQL31 未完成率较高的50%用户近三个月答卷情况 问题分析 答案重写 细节问题 SQL31 未完成率较高的50%用户近三个月答卷情况 问题分析 答案重写 细节问题 ...
mysql—排序函数rank() over()、dense_rank() over()、row_num() over()
weixin_44262119的博客
01-12 3918
转载文章 原文链接：mysql—排序函数rank() over()、dense_rank() over()、row_num() over() - beyondChan - 博客园 (cnblogs.com) 1.rank() over() 查出指定条件后进行排名。特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。 rank()over(partitionbysubjectorderbyscoredesc) rank 2.dense...
MySQL rank() over、dense_rank() over、row_number() over 用法介绍
热门推荐
大话家的博客
10-20 7万+
rank() over(业务逻辑) 作用：查出指定条件后的进行排名。特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。
MySQL中的序号开窗函数（RANK OVER）如何使用
fuxinxinxin1的博客
06-02 3020
2.2 DENSE_RANK（）OVER（列名 ORDER BY 排序方式（DESC/ASC））dense_rank() 使用排序时，列中出现相同数值时，会并列序号，但是不会像rank函数去出现不连续序号，dense_rank()会在下一个排序依然是1-n的顺序。2.1 RANK（）OVER（列名 ORDER BY 排序方式（DESC/ASC）） rank函数在使用中可以把数据进行排序，当出现排序列数值相同时进行并列排序，1-n可能出现不连续现象， 如图。当出现并列的数值时，下一个排序不会1-n的顺序。
mysql over rank,MySQL-如何进行Oracle RANK（）OVER（按分数排序）
weixin_39653448的博客
12-24 64
I'm familiar with Oracle and would normally do a RANK() (ORDER BY score DESC) AS rank to get a rank like this:Score | Rank--------------100 | 1100 | 199 | 398 | 498 | 498 | 497 | ...
MySQL学习-窗口函数over（）
最新发布
认真学编程的原始人的博客
10-20 824
窗口函数是OVER(),其中对应子句有PARTITION BY 以及 ORDER BY子句,所以形式有:
MySQL窗口函数OVER和GROUP BY语句学习
weixin_47064606的博客
03-22 3124
MySQL窗口函数使用以及对比
MySQL之rank() over(order by)
竹林幽深
08-04 1878
MySQL之rank() over(order by)
利用ArrayList对map进行按key（键）和按value (值) 排序
qq_41487251的博客
09-08 4649
利用ArrayList对map进行按key(键)和按value (值) 排序 总感觉按照值得应该比较少，意义好像也不大，感觉还没有真正懂。 使用比较器 按key（键）排序 通过ArrayList将map.entrySet()，变成list 然后在类外写具体如何比较大小的方法compare()，最后调用 Collections.sort(list, new Comparator<Map.Entry<String, String>>() import java.util.*; pub
mysql dense_rank
09-02
MySQL的DENSE_RANK函数是一种窗口函数，用于对查询结果集中的行进行排序，并给每一行分配一个密集的排名值。该函数的语法如下： DENSE_RANK() OVER (PARTITION BY column1, column2... ORDER BY columnA, columnB......
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值