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)