牛客网练习sql题2----出错和分析

1. 题目

查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
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));
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));

解答：

• select e.last_name,e.first_name,d.dept_no
  from dept_emp d
  left join employees e
  on e.emp_no=d.emp_no
  where d.dept_no is not null
  思路：员工可能存在未分配部门的情况，所以采取左连接方式时，剔除员工的部门号为null的情况✘。

• select e.last_name,e.first_name,d.dept_no
  from dept_emp d
  inner join employees e
  on e.emp_no=d.emp_no
  思路：内连接直接将符合条件的结果查询出来✔。

• select e.last_name,e.first_name,d.dept_no
  from dept_emp d
  left join employees e
  on e.emp_no=d.emp_no
  思路：员工表的员工作为全集，部门表中分配了部门的员工作为子集，查询时只需将部门表作为主表，就能实现需求✔。（于是发现我写的第一句sql虽然结果正确，但是实际上并不需要再加一个过滤条件，接着就引出下面一种写法。）

• select e.last_name,e.first_name,d.dept_no
  from employees e
  left join dept_emp d
  on e.emp_no=d.emp_no
  where d.dept_no <>’’
  这次将员工表作为主表，就需要剔除部门号为空的情况✔。

2. 题目

查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)

解答：

select e.last_name,e.first_name,d.dept_no
from employees e left join dept_emp d
on e.emp_no=d.emp_no
思路：不需要剔除员工的部门号为空的情况，只需要将全集员工表作为主表，左连接即可✔。

与上一题做对比，就是看谁作为主表的区别。

总结：

在工作中时，我常常对左连接、内连接等等连接方式，总是摸不着头脑，这一题就让我大致了解他们的使用。

左连接：以左表作为主表。
内连接：以两表的交集作为查询结果。

3. 题目

查找所有员工入职时候的薪水情况，给出 emp_no 以及 salary ， 并按照 emp_no 进行逆序(请注意，一个员工可能有多次涨薪的情况)
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));
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));

解答：

• select e.emp_no,s.salary
  from employees e left join salaries s
  on e.emp_no=s.emp_no
  where e.hire_date=s.from_date
  order by e.emp_no desc
  思路：在工资表中存在可能不止一条的员工工资记录，内连接也能实现，过滤条件要求员工被雇佣的日期等于工资的起始日期，就只会有一条员工的工资记录被查询出来✔。

• select emp,salary
  from salaries s
  where row_number() over(partition by emp_no order by from_date) r
  ✘正确版本参照下面的写法

• select emp_no,salary
  from(
  select emp_no,salary
  ,row_number() over( partition by emp_no order by from_date) r
  from salaries
  )
  where r=1
  order by emp_no desc
  思路：只需要对工资表进行分组聚合排序，row_number =1 将员工分组、工资起始日期最小的一条查询出来✔。

• select emp_no,salary
  from salaries
  group by emp_no
  having from_date=min(from_date)
  order by emp_no desc
  这种写法，牛客网是通过的，但实际上在 sqlserver 等等数据库里， salary 工资并没有包含在 group by 中，会报错，“字段 " salaries.salary " 必须出现在 GROUP BY 子句中或者在聚合函数中使用”。其次 having 后面跟的是 boolean 判断句， having 在这里是没起作用的，即去掉 having 这句，同样能通过测试 。✘