MySQL 8.0 窗户函数rank() over处理排名

---数据；学生表，课程表，成绩表

DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `course` (
  `id` int NOT NULL,
  `c_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `course`
--

LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'mysql'),(2,'oracle'),(3,'redis'),(4,'mongodb'),(5,'mss');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `score`
--

DROP TABLE IF EXISTS `score`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `score` (
  `sid` int NOT NULL,
  `cid` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `score`
--

LOCK TABLES `score` WRITE;
/*!40000 ALTER TABLE `score` DISABLE KEYS */;
INSERT INTO `score` VALUES (1,1,90,1),(1,2,80,2),(1,3,70,3),(1,4,10,4),(1,5,99,5),(2,1,12,8),(2,2,13,9),(2,3,100,10),(2,4,99,11),(2,5,100,12),(3,1,50,13),(3,2,80,14),(3,3,90,15),(3,4,0,16),(3,5,10,17),(4,1,55,18),(4,2,88,19),(4,3,99,20),(4,4,100,21),(4,5,90,22),(5,1,88,23),(5,2,90,24),(5,3,90,25),(5,4,77,26),(5,5,66,27),(6,1,54,28),(6,2,58,29),(6,3,93,30),(6,4,79,31),(6,5,65,32);
/*!40000 ALTER TABLE `score` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `stu`
--

DROP TABLE IF EXISTS `stu`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stu` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stu`
--

LOCK TABLES `stu` WRITE;
/*!40000 ALTER TABLE `stu` DISABLE KEYS */;
INSERT INTO `stu` VALUES (1,'张三'),(2,'李四'),(3,'王五'),(4,'张非'),(5,'李思思'),(6,'王即墨');
/*!40000 ALTER TABLE `stu` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-05-03  7:34:10

---根据课程成绩排名：

mysql> select stu.name,c_name,score,rank() over(partition by s.cid order by s.score desc) 名次 from score s , stu ,course c where stu.id=s.sid and s.cid=c.id;
+-----------+---------+-------+--------+
| name      | c_name  | score | 名次   |
+-----------+---------+-------+--------+
| 张三      | mysql   |    90 |      1 |
| 李思思    | mysql   |    88 |      2 |
| 张非      | mysql   |    55 |      3 |
| 王即墨    | mysql   |    54 |      4 |
| 王五      | mysql   |    50 |      5 |
| 李四      | mysql   |    12 |      6 |
| 李思思    | oracle  |    90 |      1 |
| 张非      | oracle  |    88 |      2 |
| 张三      | oracle  |    80 |      3 |
| 王五      | oracle  |    80 |      3 |
| 王即墨    | oracle  |    58 |      5 |
| 李四      | oracle  |    13 |      6 |
| 李四      | redis   |   100 |      1 |
| 张非      | redis   |    99 |      2 |
| 王即墨    | redis   |    93 |      3 |
| 王五      | redis   |    90 |      4 |
| 李思思    | redis   |    90 |      4 |
| 张三      | redis   |    70 |      6 |
| 张非      | mongodb |   100 |      1 |
| 李四      | mongodb |    99 |      2 |
| 王即墨    | mongodb |    79 |      3 |
| 李思思    | mongodb |    77 |      4 |
| 张三      | mongodb |    10 |      5 |
| 王五      | mongodb |     0 |      6 |
| 李四      | mss     |   100 |      1 |
| 张三      | mss     |    99 |      2 |
| 张非      | mss     |    90 |      3 |
| 李思思    | mss     |    66 |      4 |
| 王即墨    | mss     |    65 |      5 |
| 王五      | mss     |    10 |      6 |
+-----------+---------+-------+--------+
30 rows in set (0.00 sec)

---名次并列时用dense_rank()处理

mysql> select stu.name,c_name,score,dense_rank() over(partition by s.cid order by s.score desc) 名次 from score s , stu ,course c where stu.id=s.sid and s.cid=c.id;
+-----------+---------+-------+--------+
| name      | c_name  | score | 名次   |
+-----------+---------+-------+--------+
| 张三      | mysql   |    90 |      1 |
| 李思思    | mysql   |    88 |      2 |
| 张非      | mysql   |    55 |      3 |
| 王即墨    | mysql   |    54 |      4 |
| 王五      | mysql   |    50 |      5 |
| 李四      | mysql   |    12 |      6 |
| 李思思    | oracle  |    90 |      1 |
| 张非      | oracle  |    88 |      2 |
| 张三      | oracle  |    80 |      3 |
| 王五      | oracle  |    80 |      3 |
| 王即墨    | oracle  |    58 |      4 |
| 李四      | oracle  |    13 |      5 |
| 李四      | redis   |   100 |      1 |
| 张非      | redis   |    99 |      2 |
| 王即墨    | redis   |    93 |      3 |
| 王五      | redis   |    90 |      4 |
| 李思思    | redis   |    90 |      4 |
| 张三      | redis   |    70 |      5 |
| 张非      | mongodb |   100 |      1 |
| 李四      | mongodb |    99 |      2 |
| 王即墨    | mongodb |    79 |      3 |
| 李思思    | mongodb |    77 |      4 |
| 张三      | mongodb |    10 |      5 |
| 王五      | mongodb |     0 |      6 |
| 李四      | mss     |   100 |      1 |
| 张三      | mss     |    99 |      2 |
| 张非      | mss     |    90 |      3 |
| 李思思    | mss     |    66 |      4 |
| 王即墨    | mss     |    65 |      5 |
| 王五      | mss     |    10 |      6 |
+-----------+---------+-------+--------+
30 rows in set (0.00 sec)