mysql第三次打卡

#作业#

项目七: 各部门工资最高的员工（难度：中等）

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
创建表employee表

CREATE TABLE employee(`id` INT NOT NULL PRIMARY KEY, `name` VARCHAR(225),`salary` INT NOT NULL, `department_id` INT NOT NULL);
INSERT INTO employee
VALUES
  (1,'Joe',70000,1),
	(2,'Henry',80000,2),
	(3,'Sam',60000,2),
	(4,'Max',90000,1),

结果如下:

id name salary department_id
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1

创建Department 表

CREATE TABLE department(`id` INT NOT NULL PRIMARY KEY,`name` VARCHAR(225));
INSERT INTO department
VALUES
  (1,'IT'),
	(2,'Sales');
SELECT * FROM department

结果如下:

id name
1	IT
2	Sales

编写一个 SQL 查询，找出每个部门工资最高的员工。

SELECT department.`name` AS `department`,t.employee,t.salary
FROM
(SELECT e1.name Employee,e1.salary Salary,e1.department_id
FROM employee e1 LEFT JOIN employee e2 
on e1.salary < e2.salary AND e1.department_id = e2.department_id
WHERE e2.id is NULL)t
JOIN department ON department.id = t.department_id;

输出结果：

department  name salary
Sales	Henry	80000
IT	Max	90000

若所有的salary里面没有重复的也可以选用另外一种方法：

FROM employee 
LEFT JOIN department ON department.id = employee.department_id
WHERE salary in 
  (SELECT MAX(salary) FROM employee GROUP BY department_id);

因为 SELECT MAX(salary) FROM employee GROUP BY department_id返回的数字：

MAX(salary)
90000
80000

注意：当在employee表中所有salary 都不在重复的时候结果为：

department  name salary
IT	Max	90000
Sales	Henry	80000

但在employee中加入一条数据（5，‘Mark’,80000,1）时，输出结果为：

department  name salary
IT	Max	90000
IT	Mark	80000
Sales	Henry	80000

项目八: 换座位（难度：中等）
创建seat表如下：

id  student
1	Abbou
2	Doris
3	Green
4	Emerson
5	Jeames

题目：改变相邻俩学生的座位，如果学生人数是奇数，则不需要改变最后一个同学的座位。

SELECT t.id,t.student
FROM
(SELECT id-1 id,student FROM seat
WHERE MOD(id,2) = 0
UNION
SELECT id+1 id,student FROM seat
WHERE MOD(id,2) = 1 AND id != (SELECT MAX(id) FROM seat)
UNION
SELECT id,student FROM seat
WHERE MOD(id,2) = 1 AND id = (SELECT MAX(id) FROM seat))t
ORDER BY id;

输出结果如下：

id  student
1	Doris
2	Abbou
3	Emerson
4	Green
5	Jeames

项目九: 分数排名（难度：中等）
创建score表如下：

id  score
1	3.5
2	3.65
3	4
4	3.85
5	4
6	3.65

题目：编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

SELECT t.score,t.rank
FROM score
LEFT JOIN
(SELECT rank() over(ORDER BY score DESC) as `rank`,score 
FROM score
GROUP BY score)t
ON t.score =  score.score
ORDER BY t.rank 

输出结果如下：

score rank
4	    1
4   	1
3.85	2
3.65	3
3.65	3
3.5	    4