选出各部门工资最高的员工

最新推荐文章于 2024-05-29 00:59:38 发布

adiarqi

最新推荐文章于 2024-05-29 00:59:38 发布

阅读量759

点赞数 1

分类专栏： mysql学习文章标签：索引使用 group by注意点存储过程及函数编写

本文链接：https://blog.csdn.net/qq_28376741/article/details/90368904

版权

mysql学习专栏收录该内容

4 篇文章 0 订阅

订阅专栏

记一次mysql面试题

基础数据准备

/**创建测试库**/
create database test;
/**创建部门表**/
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*编号*/
dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
/** Innodb索引**/;
CREATE TABLE `test`.`dept` (
  `deptno` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dname` VARCHAR(45) NOT NULL,
  `loc` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`deptno`)) ENGINE=Innodb DEFAULT CHARSET=utf8;
/** MyISAM 索引**/
CREATE TABLE `test`.`dept` (
  `deptno` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dname` VARCHAR(45) NOT NULL,
  `loc` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`deptno`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/**创建员工表**/
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
/** MyISAM 索引**/
CREATE TABLE `test`.`emp` (
  `empno` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ename` VARCHAR(20) NOT NULL,
  `job` VARCHAR(9) NOT NULL,
  `mgr` MEDIUMINT NOT NULL,
  `hiredate` DATE NOT NULL,
  `sal` DECIMAL(7,2) NOT NULL,
  `comm` DECIMAL(7,2) NOT NULL,
  `deptno` MEDIUMINT NOT NULL,
  PRIMARY KEY (`empno`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/** Innodb索引**/
CREATE TABLE `test`.`emp` (
  `empno` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ename` VARCHAR(20) NOT NULL,
  `job` VARCHAR(9) NOT NULL,
  `mgr` MEDIUMINT NOT NULL,
  `hiredate` DATE NOT NULL,
  `sal` DECIMAL(7,2) NOT NULL,
  `comm` DECIMAL(7,2) NOT NULL,
  `deptno` MEDIUMINT NOT NULL,
  PRIMARY KEY (`empno`)) ENGINE=Innodb DEFAULT CHARSET=utf8;

/**创建随机生成字符串函数**/
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do
 set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
 set i = i + 1;
 end while;
 return return_str;
 end $$
/** 删除函数**/
drop function rand_string$$
/**创建随机生成数字函数**/
create function rand_num(n INT) returns INT
begin
return floor(1+rand()*n);
end $$
/**创建随机生成数字函数**/
create function rand_num() returns INT begin return floor(1+rand()*10); end$$

/** 创建一个存储过程**/
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0
set autocommit = 0;  
repeat
set i = i + 1;
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),rand_num(20000),400,rand_num(10));
until i = max_num
end repeat;
commit;
end $$
/** 创建部门存储过程**/
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0
set autocommit = 0;  
repeat
set i = i + 1;
insert into dept(dname, loc) values (rand_string(8),rand_string(20));
until i = max_num
end repeat;
commit;
end $$
/** 创建员工存储过程 **/
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0
set autocommit = 0;  
repeat
set i = i + 1;
insert into emp(ename, job,mgr, hiredate,sal,comm,deptno) values (rand_string(6),'SALESMAN',0001,curdate(),rand_num(20000),400,rand_num(10));
until i = max_num
end repeat;
commit;
end $$

/**删除存储过程**/
 drop procedure insert_emp$$
 
 /**创建索引**/
create index deptnoIndex on emp(deptno);
alter table emp add primary key(empno);
ALTER TABLE `test`.`emp` CHANGE COLUMN `empno` `empno` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT ;

/**插入数据**/
call insert_emp(1, 100000);

查询出各部门最高工资最高的员工信息（员工id，员工name，员工salary

答案

mysql> select s.deptno, s.empno, s.ename, s.sal from (select * from emp order by sal desc)as s group by deptno limit 10;
+--------+-------+--------+----------+
| deptno | empno | ename  | sal      |
+--------+-------+--------+----------+
|      1 | 72239 | siLfuH | 19997.00 |
|      2 | 61024 | whwMtG | 19999.00 |
|      3 |  6081 | tGXVIF | 19998.00 |
|      4 | 20886 | uCFrVC | 19998.00 |
|      5 | 45454 | CDknLR | 20000.00 |
|      6 |  4803 | EsckQz | 19997.00 |
|      7 | 62417 | WmwuLw | 19994.00 |
|      8 | 49995 | GXGEVM | 19992.00 |
|      9 | 85071 | yTbxlM | 19995.00 |
|     10 |   254 | fphQKb | 19996.00 |
+--------+-------+--------+----------+

mysql> explain select * from (select * from emp order by sal desc)as s group by deptno limit 10;
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
|  2 | DERIVED     | emp        | ALL  | NULL          | NULL | NULL    | NULL | 100166 | Using filesort                  |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
2 rows in set (0.17 sec)

由MyISAM作为engine创建的表

## deptno上的索引没有起到作用
mysql> explain select max(sal), deptno from emp group by deptno desc limit 100;
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.01 sec)

由InnoDB作为engine创建的表

注意点：max函数同group by一起使用时，虽然sal是最大的，但是与之匹配的用户信息却是不对的（实际上取得是group by分组后的第一条记录的基本信息）

mysql>  select max(sal), deptno, empno, ename from emp group by deptno desc limit 20;
+----------+--------+--------+--------+
| max(sal) | deptno | empno  | ename  |
+----------+--------+--------+--------+
| 19996.00 |     10 |  99999 | HOXxsy |
| 19995.00 |      9 |  99993 | aCKReV |
| 19992.00 |      8 |  99978 | DHywNA |
| 19994.00 |      7 |  99995 | aqCOmT |
| 19997.00 |      6 |  99970 | pvgxSW |
| 20000.00 |      5 |  99988 | nggcTk |
| 19998.00 |      4 | 100000 | UIEZgV |
| 19998.00 |      3 |  99991 | hOvNCy |
| 19999.00 |      2 |  99998 | ubVyIS |
| 19997.00 |      1 |  99996 | moGEVN |
+----------+--------+--------+--------+
10 rows in set (0.09 sec)

mysql>  select * from emp group by deptno desc limit 20;
+--------+--------+----------+-----+------------+----------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal      | comm   | deptno |
+--------+--------+----------+-----+------------+----------+--------+--------+
|  99999 | HOXxsy | SALESMAN |   1 | 2019-05-20 |  5403.00 | 400.00 |     10 |
|  99993 | aCKReV | SALESMAN |   1 | 2019-05-20 |  6364.00 | 400.00 |      9 |
|  99978 | DHywNA | SALESMAN |   1 | 2019-05-20 |  5324.00 | 400.00 |      8 |
|  99995 | aqCOmT | SALESMAN |   1 | 2019-05-20 | 13049.00 | 400.00 |      7 |
|  99970 | pvgxSW | SALESMAN |   1 | 2019-05-20 |  1418.00 | 400.00 |      6 |
|  99988 | nggcTk | SALESMAN |   1 | 2019-05-20 |  8301.00 | 400.00 |      5 |
| 100000 | UIEZgV | SALESMAN |   1 | 2019-05-20 |   242.00 | 400.00 |      4 |
|  99991 | hOvNCy | SALESMAN |   1 | 2019-05-20 | 14879.00 | 400.00 |      3 |
|  99998 | ubVyIS | SALESMAN |   1 | 2019-05-20 |  6920.00 | 400.00 |      2 |
|  99996 | moGEVN | SALESMAN |   1 | 2019-05-20 |  1235.00 | 400.00 |      1 |
+--------+--------+----------+-----+------------+----------+--------+--------+
10 rows in set (0.13 sec)

改进：利用join和索引。

mysql> explain select max(sal), deptno from emp group by deptno desc limit 100;
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+-------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows   | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+-------+
|  1 | SIMPLE      | emp   | index | NULL          | deptnoIndex | 3       | NULL | 100157 |       |
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+-------+
1 row in set (0.00 sec)


mysql> explain select a.deptno, a.empno, a.ename, a.sal from emp a join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno limit 10;
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | a          | ALL   | deptnoIndex   | NULL        | NULL    | NULL | 100166 | Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL |     10 | Using where; Using join buffer  |
|  2 | DERIVED     | emp        | index | NULL          | deptnoIndex | 3       | NULL | 100166 |                                 |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
3 rows in set (0.08 sec)

## 因为是选出最大工资，所以对工资添加索引。
mysql> create index salIndex on emp(sal);
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select a.deptno, a.empno, a.ename, a.sal from emp a join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno limit 10;
+----+-------------+------------+-------+----------------------+-------------+---------+-------+--------+---------------------------------+
| id | select_type | table      | type  | possible_keys        | key         | key_len | ref   | rows   | Extra                           |
+----+-------------+------------+-------+----------------------+-------------+---------+-------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL                 | NULL        | NULL    | NULL  |     10 | Using temporary; Using filesort |
|  1 | PRIMARY     | a          | ref   | deptnoIndex,salIndex | salIndex    | 4       | b.sal |    500 | Using where                     |
|  2 | DERIVED     | emp        | index | NULL                 | deptnoIndex | 3       | NULL  | 100166 |                                 |
+----+-------------+------------+-------+----------------------+-------------+---------+-------+--------+---------------------------------+
3 rows in set (0.07 sec)

mysql> select a.deptno, a.empno, a.ename, a.sal from emp a join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno limit 10;
+--------+-------+--------+----------+
| deptno | empno | ename  | sal      |
+--------+-------+--------+----------+
|      1 | 72239 | siLfuH | 19997.00 |
|      2 | 61024 | whwMtG | 19999.00 |
|      3 |  6081 | tGXVIF | 19998.00 |
|      4 | 78399 | AAYtWD | 19998.00 |
|      4 | 20886 | uCFrVC | 19998.00 |
|      5 | 45454 | CDknLR | 20000.00 |
|      6 |  4803 | EsckQz | 19997.00 |
|      7 | 62417 | WmwuLw | 19994.00 |
|      8 | 49995 | GXGEVM | 19992.00 |
|      9 | 85071 | yTbxlM | 19995.00 |
+--------+-------+--------+----------+
10 rows in set (0.07 sec)

查询出各部门最高工资的员工信息（部门id，部门name，员工name，员工salary）

由InnoDB作为engine创建的表

## 方式一
mysql> explain select d.deptno, d.dname, e.empno, e.ename, e.sal from emp e join dept d where e.deptno = d.deptno and e.sal >=(select max(sal) from emp e2 where e.deptno = e2.deptno);
+----+--------------------+-------+------+---------------+-------------+---------+---------------+-------+-------------+
| id | select_type        | table | type | possible_keys | key         | key_len | ref           | rows  | Extra       |
+----+--------------------+-------+------+---------------+-------------+---------+---------------+-------+-------------+
|  1 | PRIMARY            | d     | ALL  | PRIMARY       | NULL        | NULL    | NULL          |    10 |             |
|  1 | PRIMARY            | e     | ref  | deptnoIndex   | deptnoIndex | 3       | test.d.deptno | 25041 | Using where |
|  2 | DEPENDENT SUBQUERY | e2    | ref  | deptnoIndex   | deptnoIndex | 3       | test.e.deptno | 25041 |             |
+----+--------------------+-------+------+---------------+-------------+---------+---------------+-------+-------------+
3 rows in set (0.00 sec)
## 方式二：利用索引改进
mysql> explain select a.deptno, c.dname, a.empno, a.ename, a.sal from emp a left join dept c on a.deptno = c.deptno join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno ;
+----+-------------+------------+--------+----------------------+-------------+---------+---------------+--------+---------------------------------+
| id | select_type | table      | type   | possible_keys        | key         | key_len | ref           | rows   | Extra                           |
+----+-------------+------------+--------+----------------------+-------------+---------+---------------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                 | NULL        | NULL    | NULL          |     10 | Using temporary; Using filesort |
|  1 | PRIMARY     | a          | ref    | deptnoIndex,salIndex | salIndex    | 4       | b.sal         |    500 | Using where                     |
|  1 | PRIMARY     | c          | eq_ref | PRIMARY              | PRIMARY     | 4       | test.a.deptno |      1 |                                 |
|  2 | DERIVED     | emp        | index  | NULL                 | deptnoIndex | 3       | NULL          | 100166 |                                 |
+----+-------------+------------+--------+----------------------+-------------+---------+---------------+--------+---------------------------------+
4 rows in set (0.07 sec)

select a.deptno, c.dname, a.empno, a.ename, a.sal from emp a left join dept c on a.deptno = c.deptno join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno ;
+--------+----------+-------+--------+----------+
| deptno | dname    | empno | ename  | sal      |
+--------+----------+-------+--------+----------+
|      1 | PqmlvwSZ | 72239 | siLfuH | 19997.00 |
|      2 | hLiGGACh | 61024 | whwMtG | 19999.00 |
|      3 | aVzKcfuH |  6081 | tGXVIF | 19998.00 |
|      4 | OdaQcOPF | 78399 | AAYtWD | 19998.00 |
|      4 | OdaQcOPF | 20886 | uCFrVC | 19998.00 |
|      5 | iFCXeFMV | 45454 | CDknLR | 20000.00 |
|      6 | aVzLiGTs |  4803 | EsckQz | 19997.00 |
|      7 | RfcUpTwE | 62417 | WmwuLw | 19994.00 |
|      8 | ZMLvUmzO | 49995 | GXGEVM | 19992.00 |
|      9 | OVoHvgtA | 85071 | yTbxlM | 19995.00 |
|     10 | rvgsvbUs |   254 | fphQKb | 19996.00 |
|     10 | rvgsvbUs |   473 | KXHucb | 19996.00 |
+--------+----------+-------+--------+----------+

adiarqi

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
选出各部门工资最高的员工

记一次mysql基础数据准备/**创建测试库**/create database test;/**创建部门表**/CREATE TABLE dept( /*部门表*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/dname VARCHAR(20) NOT NULL DEFAULT "", /*名称*/loc V...
复制链接

扫一扫

专栏目录