牛客网：数据库SQL实战题库笔记

牛客网原题地址

---

牛客网的测试系统是SQLite，所以有一些用法与MySQL不一致

1. 查找最晚入职员工的所有信息

select * from employees order by hire_date desc limit 0,1

方法1：若最晚入职员工只有1人，则按入职时间倒序排列，排在第一的就是目标人员。

select * from employees 
where hire_date=(select max(hire_date) from employees)

方法2：若最晚入职员工不止1人，则先查出最晚的入职日期，再用此日期作为筛选条件。

---

2. 查找入职员工时间排名倒数第三的员工所有信息

select * from employees order by hire_date desc limit 2,1

此题考查order by和limit用法：

• “入职时间排名倒数”，使用order by hire_date desc
• “第三名”，位置排在3，且为1人，使用limit 2,1。排序从0开始，若此题改为“第一名”，则用limit 0,1（此处0可省略）。

---

3. 查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no

感觉题目有点问题：增加一句“输出结果按emp_no递增排序”就没异议了。
题干中的“输出描述”已经暗含了此排序，输出结果必须与之完全一致才能通过（这确实有些不合理）。

解法1：salaries作为主表，salaries的主键是emp_no，结果按emp_no递增排序

select s.* ,d.dept_no from salaries as s 
join dept_manager as d on s.emp_no=d.emp_no
where s.to_date = '9999-01-01'
and d.to_date='9999-01-01'

解法2：dept_manager作为主表，dept_manager的主键是dept_no，结果按dept_no递增排序

select s.* ,d.dept_no from salaries as s
inner join dept_manager as d on s.emp_no=d.emp_no
where s.to_date = '9999-01-01'
and d.to_date='9999-01-01'
order by s.emp_no  #比解法1多了一步按emp_no递增排序

• 表salaries里，1个人可能会发多次工资，对应多个to_date，因此用s.to_date = '9999-01-01’筛选“当前薪水”。
• 表dept_manager里，1个部门可能有过多个领导，对应多个to_date，因此用d.to_date = '9999-01-01’筛选“部门当前领导”。

---

4. 查找所有已经分配部门的员工的last_name和first_name以及dept_no

select e.last_name,e.first_name,d.dept_no 
from dept_emp as d
join employees as e on d.emp_no=e.emp_no
where dept_no is not null;

• “已经分配部门的员工”，所以dept_no不能为空

---

5. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

select e.last_name,e.first_name,d.dept_no 
from employees as e
left join dept_emp as d on e.emp_no=d.emp_no

• 与题目4的差别：“包括展示没有分配具体部门的员工”，不用排除dept_no为空的情况。

---

6. 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

select e.emp_no,s.salary
from employees as e
left join salaries as s
on e.emp_no=s.emp_no 
and e.hire_date=s.from_date
order by e.emp_no desc

• “入职时候的薪水”，即发薪的from_date等于入职日期，employees.hire_date=salaries.from_date

---

7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

select emp_no,count(emp_no) as t 
from salaries group by emp_no having t > 15

虽然本题通过了，但严格来讲不够严谨。本题每出现一次工资就认为涨幅1次，但可能不变，也可能降薪。

---

8. 找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次，并按照逆序显示

select distinct salary from salaries 
where to_date='9999-01-01' 
group by emp_no 
order by salary desc

• “当前”，to_date=‘9999-01-01’；
• “相同的薪水只展现一次”，用distinct salary去重；
• 同1个员工有多次发薪资的记录，因此需要去重group by emp_no，不过- 此题不用此步结果也正确，说明数据没有此情况；
• “逆序”，order by salary desc。

---

9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=‘9999-01-01’

select d.dept_no,s.emp_no,s.salary
from salaries as s
left join dept_manager as d
on s.emp_no=d.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'

同题目3

---

10. 获取所有非manager的员工emp_no

select emp_no from employees
where emp_no not in 
(
    select emp_no from dept_manager
)

• dept_manager中的emp_no都是manager
• 从employees过滤掉dept_manager中的emp_no

---

11. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

select de.emp_no,dm.emp_no as manager_no
from dept_emp as de
left join dept_manager as dm
on de.dept_no=dm.dept_no 
and dm.to_date='9999-01-01'
where de.emp_no <> dm.emp_no

• “当前manager”，dm.to_date=‘9999-01-01’
• “manager是自己的话结果不显示”，de.emp_no <> dm.emp_no

---

12. 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

select d.dept_no,d.emp_no,max(salary)
from dept_emp as d
left join salaries as s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01'
group by d.dept_no

• “当前员工”，d.to_date=‘9999-01-01’
• “部门最高薪水”，先用group by d.dept_no按部门分组，再用max(salary)找出各部门最高薪水

---

13. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

select title,count(*) as t from titles
group by title
having t>=2

• “按照title进行分组”，group by title
• “每组个数大于等于2”，having t>=2

---

14. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。

解法1：

• 先创建一个包含title和不重复emp_no的临时表，select distinct emp_no,title from titles
• 再对临时表进行分组和筛选，group by title, having t>=2

select title,count(1) as t from
(select distinct emp_no,title from titles) 
group by title
having t>=2

解法2：

• 基于title分组，group by title
• “每组个数大于等于2”，having t>=2
• “忽略重复的emp_no”，count(distinct emp_no)

select title,count(distinct emp_no) as t from titles
group by title 
having t>=2

15. 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees
where emp_no % 2 = 1
and last_name <> "Mary"
order by  hire_date desc

• “奇数”，余数=1，emp_no %2 = 1
• “last_name不为Mary”，last_name <> “Mary”
• 逆序，order by desc

延展思考：判断奇数偶数常用办法

1.位运算：位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

• 按位与
  位运算是对数的二进制进行的运算，利用按位与操作，就是 & 。奇数的二进制数的最后一位永远是 1，与 1 按位且只会得到 1，偶数相反。

select * from table where id&1 #筛选奇数

• 右移1和左移1
  右移1（>>1）相当于十进制除以2，左移1（<<1）相当于十进制乘以2，先乘2再除2，若与原数相等则是偶数，不等是奇数。

select * from table where id<>(id>>1)<<1 #筛选奇数
select * from table where id=(id>>1)<<1 #筛选偶数

---

2. id计算

• 求余数：%2或MOD(id,2)，除以2余数是1则为奇数，余数是0则为偶数

select * from table where id%2=1 #筛选奇数
select * from table where id%2=0 #筛选偶数

select * from table where mod(id,2)=1 #筛选奇数
select * from table where mod(id,2)=0 #筛选偶数

• -1的奇数次方和偶数次方

select * from table where power(-1,id)=-1 #筛选奇数
select * from table where power(-1,id)=1 #筛选偶数

---

16. 统计出当前各个title类型对应的员工当前（to_date=‘9999-01-01’）薪水对应的平均工资。结果给出title以及平均工资avg。

select title