练习:
1. 各部门工资最高的员工
练习所需的表
员工表
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
部门表
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
问题:编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
答案:
select
d.name,
em.name,
em.salary
from employee as em
inner join department as d
on em.department_Id= d.id
where em.salary in (select max(salary) from employee
group by department_Id)
order by salary desc;
2.换座位
问题:小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。其中纵列的id是连续递增的
小美想改变相邻俩学生的座位
换座位之前:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
换座位后:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
答案:
select
(
case when mod(id,2) !=0 then id+1
when mod(id,2) =0 then id-1
end
) as 'id', student from stu3
order by id;
3. 分数排名
问题:如果两个分数相同,则两个分数排名(Rank)相同
未排序:
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
排序后(序列号连续)
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
答案:
select score,dense_rank() over ( order by score desc) as R
from s;
排序后(序列号不连续)
答案:
select score,rank() over ( order by score desc) as R
from s;
4.出现三次及以上的数字
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
查找所有至少连续出现三次的数字
答案(有bug,待完善):
select n1.num
from numble as n1,numble as n2,numble as n3
where n1.id+1 = n2.id and n2.id +1=n3.id and n1.num =n2.num and n2.num = n3.num;
查询出现3次及以上的数字
答案:
select num
from
(select num,count(num) as 'ConsecutiveNums'
from numble
group by num ) as new
where ConsecutiveNums >=3;
6.至少有五名直接下属的经理
+------+----------+-----------+----------+
|Id |Name |Department |ManagerId |
+------+----------+-----------+----------+
|101 |John |A |null |
|102 |Dan |A |101 |
|103 |James |A |101 |
|104 |Amy |A |101 |
|105 |Anne |A |101 |
|106 |Ron |B |101 |
+------+----------+-----------+----------+
查询结果:
+-------+
| Name |
+-------+
| John |
+-------+
答案:
select em.name
from (select ManagerId,count(ManagerId) as 'times'
from employee
group by ManagerId
) as new inner join employee as em
on new.ManagerId = em.id
where times >=5;
7. 查询回答率最高的问题
数据表:
答案:
select new.question_id,count(new.question_id) as 'answer_num'
from (select action,question_id
from question
where action= 'answer') as new
group by new.question_id
order by answer_num desc;
8. 各部门前3高工资的员工
数据表:
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
查询结果:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
答案(语句有改善空间):
select Department,Employee,salary
from (
select Department,Employee,salary,
rank() over (PARTITION BY Department order by salary desc) as ranking
from (
select d.name as 'Department',em.name as 'Employee',em.salary as 'salary'
from employee as em inner join department as d
on d.id= em.department_Id ) as d_salary
)as display
where display.ranking <= 3;