MySQL(三)

练习1：找出各部门工资最高的员工
题目要求：

思路：
首先依据要求将两个表格进行组合成一个表格，再加入一个表格（依据部门进行分组且取个部门最大薪水）组合显示
1—两个表格的创建及数据载入

create table employee(

id int unsigned primary key not null auto_increment,
name varchar(20) not null,
salary int unsigned,
departmentid int unsigned



);

insert into employee values(1,"Joe",70000,1);
insert into employee values(2,"Henry",80000,2);
insert into employee values(3,"Sam",60000,2);
insert into employee values(4,"Max",90000,1);

create table department(

id int unsigned primary key not null,
name varchar(20) not null

);

insert into department values(1,"IT");
insert into department values(2,"Sales");

2—操作

select a.* from (select department.name as department,employee.name,employee.salary from employee,department  where employee.departmentid=department.id) as a
join(
select max(salary) as salary from employee group by departmentid) as g where a.salary=g.salary ;

3—效果展示

练习2：换座位，交换相邻两项顺序，末位为奇数为则不进行交换
题目要求：

思路：
按照id的奇数偶数性进行分类处理，若为奇数且不是末位奇数，则id值加1，是末位奇数则不变id值，若为偶数id，则id值减1。
1—表格创建及插入数值

create table seat(

id int unsigned primary key not null auto_increment,
student varchar(255) not null 



);
insert into seat values(1,"Abbot"),(2,"Doris"),(3,"Emerson"),(4,"Green"),(5,"Jeames");
select * from seat;

2—操作

select id+1 as id,student from seat where id mod 2=1 and id <> (select count(*) from seat)

select id,student from seat where id mod 2=1 and id=(select count(*) from seat)

select id-1,student from seat where id mod 2=0

联合

(select id+1 as id,student from seat where id mod 2=1 and id <> (select count(*) from seat) union select id,student from seat where id mod 2=1 and id=(select count(*) from seat) union select id-1,student from seat where id mod 2=0) as a

实现

select * from (select id+1 as id,student from seat where id mod 2=1 and id <> (select count(*) from seat) union select id,student from seat where id mod 2=1 and id=(select count(*) from seat) union select id-1,student from seat where id mod 2=0) as a order by a.id;

3—效果展示

练习3：分数排名，且名次之间无“间隔”
题目要求：

1—表格的创建及数据插入

create table score(

id int unsigned not null primary key auto_increment,

score decimal(3,2)


);

insert into score values(1,3.50),(2,3.65),(3,4.00),(4,3.85),(5,4.00),(6,3.65);
select * from score;

2—操作

select dense_rank() over (order by score desc) as rank1,score from score;

3—效果展示