#.查询每个部门薪水最高的人

CREATE TABLE departments (
id int NOT NULL,
name varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE employees (
id int NOT NULL,
name varchar(20) COLLATE utf8_bin DEFAULT NULL,
department_id int DEFAULT NULL,
salary int DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

insert into departments(id,name)
values(100,‘研发部’),(101,‘市场部’),(102,‘销售部’);

insert into employees(id,name,department_id,salary)
values(2000,‘赵’,100,17000),(2001,‘钱’,101,11000),
(2002,‘孙’,102,11000),
(2003,‘李’,100,8000),
(2004,‘周’,101,11000),
(2005,‘吴’,102,7000),
(2006,‘郑’,100,8000),
(2007,‘王’,101,11000),
(2008,‘冯’,102,12000),
(2009,‘陈’,100,8000),
(2010,‘褚’,101,15000),
(2011,‘卫’,102,12000),
(2012,‘蒋’,101,14000);
#方法1
select t1.id,t1.department_id,t1.name,t1.salary from employees t1 join
(select max(salary) maxsalary, department_id from employees group by department_id) t2
on t1.department_id = t2.department_id and t1.salary=t2.maxsalary;
#方法2
select * from employees e where(e.department_id,e.salary)
in
(select e1.department_id,max(e1.salary)
from employees e1 group by department_id);

select *from employees e where
e.salary =(select max(e1.salary) from employees e1 where e1.department_id = e.department_id) ;