本文共 7236 字，大约阅读时间需要 24 分钟。

SQL是每个学CS的必备技能，看上去内容就是些简单的建库建表、修表修值，增删查改，子查询，表连接，索引，存储过程，触发器，实则很难精通，遇到写复杂的SQL，反应半天也很难写出，看到别人写的答案，顿觉得豁然开朗。

下面记录写在牛客网上的刷题时遇到的不错的SQL题目以及对应的解题思路，以备自己复习之用。加油，秋招还未结束，抓住秋招最后的尾巴。。。

题目中主要用到的table表如下定义：

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

部门经理表dept_manager

CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL,`emp_no` int(11) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));

部门员工表dept_emp

CREATE TABLE `dept_emp` (`emp_no` int(11) NOT NULL,`dept_no` char(4) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));

薪水表salaries

CREATE TABLE `salaries` (`emp_no` int(11) NOT NULL,`salary` int(11) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`from_date`));

员工职称表titles

CREATE TABLE IF NOT EXISTS "titles" (`emp_no` int(11) NOT NULL,`title` varchar(50) NOT NULL,`from_date` date NOT NULL,`to_date` date DEFAULT NULL);

部门表departments

CREATE TABLE `departments` (`dept_no` char(4) NOT NULL,`dept_name` varchar(40) NOT NULL,PRIMARY KEY (`dept_no`));

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

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

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

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

3. 查找所有已经分配部门的员工的last_name和first_name

select last_name,first_name,d.dept_no from employees e ,dept_emp dwhere d.emp_no=e.emp_no    and d.dept_no not NULL;

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

select e.emp_no from employees ewhere e.emp_no not in (select d.emp_no from dept_manager d);

5. 获取所有员工当前的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 de join dept_manager dmon de.dept_no=dm.dept_nowhere de.emp_no!=dm.emp_no    and dm.to_date='9999-01-01'    and de.to_date='9999-01-01';

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

select title,count(distinct(emp_no)) as t from titlesgroup by title having count(*)>=2;

7. 统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

select title,avg(salary) as avg from titles join salarieson titles.emp_no=salaries.emp_nowhere salaries.to_date='9999-01-01'    and titles.to_date='9999-01-01'group by title;

8. 查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

select e.emp_no,max(salary) salary,last_name,first_name from employees ejoin salaries s on e.emp_no=s.emp_nowhere s.to_date='9999-01-01'    and salary  not in (select max(salary) from salaries);

9. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

select last_name,first_name,dept_name from employees e left join dept_emp de on e.emp_no=de.emp_noleft join departments d on de.dept_no=d.dept_no;

10. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

-- 严谨的思路select (    (select salary from salaries where emp_no=10001 order by to_date desc limit 1)-    (select salary from salaries where emp_no=10001 order by to_date limit 1)) as growth;-- 不严谨，必须最后一次工资大于最初工资/*select (max(salary)-min(salary)) as growth from salaries where emp_no=10001;*/

11. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

select tb1.emp_no,(salary_now - salary_start) as growth from (select emp_no,salary as salary_now from salaries     where to_date='9999-01-01') as tb1join (select e.emp_no ,salary as salary_start from employees e     join salaries s on e.emp_no=s.emp_no       where from_date=hire_date) as tb2on tb1.emp_no=tb2.emp_noorder by growth;

12. 统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

select d.dept_no,dept_name,count(s.from_date) as sum from departments djoin dept_emp de on d.dept_no=de.dept_nojoin salaries s on de.emp_no=s.emp_nogroup by d.dept_no;

13. 对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

select s1.emp_no,s1.salary,count(distinct s2.salary) as rankfrom salaries s1,salaries s2where s1.to_date='9999-01-01'     and s2.to_date='9999-01-01'    and s1.salary<=s2.salarygroup by s1.emp_noorder by s1.salary desc,s1.emp_no asc;

14. 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
    结果第一列给出员工的emp_no，
    第二列给出其manager的manager_no，
    第三列给出该员工当前的薪水emp_salary,
    第四列给该员工对应的manager当前的薪水manager_salary

select emp_tb.emp_no,manager_no,emp_tb.salary,manager_tb.salaryfrom(select de.emp_no,dept_no,salary from dept_emp de join salaries s     on de.emp_no=s.emp_no     where de.to_date='9999-01-01' and s.to_date='9999-01-01') as emp_tbjoin (select dm.emp_no as manager_no,dept_no,salary from dept_manager dm join salaries s    on dm.emp_no=s.emp_no     where dm.to_date='9999-01-01' and s.to_date='9999-01-01') as manager_tbon emp_tb.dept_no=manager_tb.dept_no    and emp_tb.salary>manager_tb.salary;

15. 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

select d.dept_no,dept_name,title,count(title) as count from departments djoin dept_emp de on d.dept_no=de.dept_nojoin titles t on de.emp_no=t.emp_nowhere de.to_date='9999-01-01'    and t.to_date='9999-01-01'group by d.dept_no,title;

16. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)

select s1.emp_no,s1.from_date,(s1.salary-s2.salary) as salary_growthfrom salaries s1,salaries s2where s1.emp_no=s2.emp_no    and salary_growth>5000    and (strftime('%Y',s1.to_date)-strftime('%Y',s2.to_date)=1        or strftime('%Y',s1.from_date)-strftime('%Y',s2.from_date)=1)order by salary_growth desc;

17. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

select avg(salary) as avg_salary from salaries where salary not in (select max(salary) from salaries)     and salary not in (select min(salary) from salaries)     and to_date='9999-01-01';

18. 获取employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

select first_name from employees order by substr(first_name,length(first_name)-1);

19. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

select dept_no,group_concat(emp_no) as employeesfrom dept_emp group by dept_no;

20. 使用含有关键字exists查找未分配具体部门的员工的所有信息。

-- 方法1/*select * from employees where not exists (select emp_no from dept_emp where emp_no = employees.emp_no);*/-- 方法2select * from employees where emp_no not in (select emp_no from dept_emp);

21. 给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
    

select e.emp_no,first_name,last_name,eb.btype,salary,(case     when btype=1 then 0.1*salary    when btype=2 then 0.2*salary    else 0.3*salary    end)as bonusfrom employees e,salaries s,emp_bonus ebwhere e.emp_no=s.emp_no    and e.emp_no=eb.emp_no    and s.to_date='9999-01-01';

22. 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
    

select s1.emp_no,s1.salary,(select sum(s2.salary) from salaries s2  where s2.emp_no<=s1.emp_no and s2.to_date='9999-01-01') as running_totalfrom salaries s1 where s1.to_date='9999-01-01'order by s1.emp_no;

23. 对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name
    

select e1.first_name from employees e1where (select count(*) from employees e2       where e1.first_name<=e2.first_name)%2=1;

这些题都是很常见的SQL题目，但是简短时间内，很难写出，或者容易出错。

转载地址：http://fslrb.baihongyu.com/