Task 07 决胜秋招
Content
- Section A(只写了练习一
- Section B(没写
- Section C(没写
Reference：
[1]GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL，决胜秋招。
[2]笔试题 sql语句求出各部门工资最高的员工（leetcode）_纱王的博客-CSDN博客
缓慢更新中
Section A
练习一: 各部门工资最高的员工（难度：中等）
- 创建 Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id
create database if not exists section_a;
use section_a;
drop table if exists emp;
create table emp(
-> Id char(1) not null,
-> Name varchar(100) not null,
-> Salary int not null,
-> Departmentid int,
-> primary key(Id));
create table dept(
-> Id char(1) not null,
-> Name varchar(100) not null,
-> primary key(Id));
select * from emp;
select * from dept;
1.1.png
有没有人知道怎么改大小啊/(ㄒoㄒ)/~~.png
-
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
1.2尝试.png
第一次尝试发现输出的salary是最高的，但是名字对不上，筛选出了每个部门的第一个人，考虑应该先把两个表join起来再筛选
-- 法一(join)
/*先将两个表合起来*/
select dept.name as Department
,emp.name as Employee
,emp.salary as Salary
from dept inner join emp on dept.id=emp.departmentid
/*对合起来的表筛选*/
where
emp.salary in (
select max(salary)
from emp
group by departmentid);
-- 法二(关联子查询)
select d.name as Department
,e.name as Employee
,e.salary as Salary
from dept d,emp e
where e.departmentid=d.id
and
e.salary=(
select max(salary)
from emp
where departmentid=d.id);
-- 法三(中间表-来源于CSDN)
select d.name Department
,e.name Employee
,tb.salary Salary
from
emp e, dept d,(select departmentId ,max(salary) salary from emp group by departmentId) tb
where e.salary=tb.salary
and tb.departmentId=e.departmentId
and tb.departmentId=d.Id
有几个容易错的地方：
1.where的时候不能用别名：
FROM WHERE GROUP BY HAVING SELECT ORDER BY
但是！表的别名必须用！不然会ERROR 1054 (42S22): Unknown column 'emp.departmentid' in 'where clause'
2.使用group by的时候，出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的字段。最后一步筛选最高薪水也可以写成只要select里外一一对应就行
where
(e.departmentid,e.salary) in (
select departmentid,max(salary)
from emp
group by departmentid);
练习二: 换座位（难度：中等）
- 小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
请创建如下所示 seat 表：
+---------+---------+
- | id | student |
+---------+---------+
- | 1 | Abbot |
5 | 2 | Doris |
- | 3 | Emerson |
- | 4 | Green |
- | 5 | Jeames |
- +---------+---------+
假如数据输入的是上表，则输出结果如下：(注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。)
+---------+---------+
- | id | student |
- +---------+---------+
- | 1 | Doris |
5 | 2 | Abbot |
- | 3 | Green |
- | 4 | Emerson |
- | 5 | Jeames |
- +---------+---------+
练习三: 分数排名（难度：中等）
- 编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
创建以下 score 表：
+----+-------+
- | Id | Score |
- +----+-------+
- | 1 | 3.50 |
5 | 2 | 3.65 |
- | 3 | 4.00 |
- | 4 | 3.85 |
- | 5 | 4.00 |
- | 6 | 3.65 |
10 +----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
+-------+------+
- | Score | Rank |
- +-------+------+
- | 4.00 | 1 |
5 | 4.00 | 1 |
| 3.85 | 2 |
- | 3.65 | 3 |
- | 3.65 | 3 |
- | 3.50 | 4 |
10 +-------+------+
练习四：连续出现的数字（难度：中等）
- 编写一个 SQL 查询，查找所有至少连续出现三次的数字
