MySQL题目练习(正在更新中)
2022/7/29 2:52:41
本文主要是介绍MySQL题目练习(正在更新中),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
目录- 练习环境
- 第一题
练习环境
MySQL版本为5.6
使用的软件 navicate
第一题
题目:统计每个部门中薪酬最高的员工
知识点:分组排序,多表查询
解题思路:
插入数据
CREATE TABLE `EMPLOYEE` ( `ID` varchar(255), `Name` varchar(255), `Salary` varchar(255), `DepartmentID` varchar(255) ); INSERT INTO `EMPLOYEE` VALUES ('1', 'Joe', '70000', '1'); INSERT INTO `EMPLOYEE` VALUES ('2', 'Henry', '80000', '2'); INSERT INTO `EMPLOYEE` VALUES ('3', 'Jane', '60000', '2'); INSERT INTO `EMPLOYEE` VALUES ('4', 'Ben', '90000', '1'); CREATE TABLE `DEPARTMENT` ( `ID` varchar(255), `DEPARTMENT` varchar(255) ); INSERT INTO `DEPARTMENT` VALUES ('1', 'IT'); INSERT INTO `DEPARTMENT` VALUES ('2', 'Sales');
点击查看答案
# 方法一 SELECT `Name`, Salary, DEPARTMENT FROM ( EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.DepartmentID = DEPARTMENT.ID ) INNER JOIN ( SELECT DepartmentID, MAX( Salary ) AS MAX_Salary FROM EMPLOYEE GROUP BY DepartmentID ) AS T ON EMPLOYEE.DepartmentID = T.DepartmentID AND EMPLOYEE.Salary = T.MAX_Salary; # 方法二 SELECT * FROM (SELECT `Name` ,Salary,DEPARTMENT as D FROM EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.DepartmentID = DEPARTMENT.ID ORDER BY D,Salary DESC ) as t GROUP BY D ;
这篇关于MySQL题目练习(正在更新中)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-25如何部署MySQL集群资料:新手入门教程
- 2024-12-24MySQL集群部署资料:新手入门教程
- 2024-12-24MySQL集群资料详解:新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习:新手入门教程
- 2024-12-24部署MySQL集群入门:一步一步搭建指南
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南