sql多表连接学习（1）

1.现有电影信息表film，包含以下字段：

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

有类别表category，包含以下字段：

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

电影分类表film_category，包含以下字段：

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

使用join查询方式找出没有分类的电影id以及其电影名称。

 

方法一：

法一：内连接+not in

 

1 2 3 4

select film_id as '电影id',title as '名称' from film where film_id not in(select f.film_id from film f inner join film_category fc on f.film_id=fc.film_id)

法二：左连接+is null

 

1 2 3

select f.film_id, f.title from film f left join film_category fc on f.film_id=fc.film_id where fc.category_id is null

方法三：

思路：有两种解法可以解决该问题

      2.使用 inner join 将三个表连接，查询出共有的电影id，在通过共有电影id与 film 表中的 film_id 做 not in 即可

      3.使用 left join 依次连接 film、film_category、category三个表，最后通过条件判断 category_id is null，取出所需的信息

思路一：

1 2 3 4 5 6 7

select film_id, title from film where film_id not in (     select f.film_id     from film f, film_category fc, category c     where f.film_id = fc.film_id and fc.category_id = c.category_id )

思路二

1 2 3 4 5 6 7

select f.film_id, f.title from film f left join film_category fc on f.film_id = fc.film_id left join category c on fc.category_id = c.category_id where c.category_id is null

 

2.有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	2001-06-22
10002	1964-06-02	Bezalel	Simmel	F	1999-08-03

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	85097	2001-06-22	2002-06-22
10001	88958	2002-06-22	9999-01-01
10002	72527	1999-08-03	2000-08-02
10002	72527	2000-08-02	2001-08-02

请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为

（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）

emp_no	growth
10001	3861

思路一：

select a.emp_no, (b.salary - c.salary) as growth

from

employees as a

    inner join salaries as b

    on a.emp_no = b.emp_no and b.to_date = '9999-01-01'

    inner join salaries as c

    on a.emp_no = c.emp_no and a.hire_date = c.from_date

order by growth asc

 

思路二:

select b.emp_no,(b.salary-a.salary) as growth

from

(select e.emp_no,s.salary

from employees e left join salaries  s on e.emp_no=s.emp_no

and e.hire_date=s.from_date)a -- 入职工资表

inner join

(select e.emp_no,s.salary

from employees e left join salaries  s on e.emp_no=s.emp_no

where s.to_date='9999-01-01')b -- 现在工资表

on a.emp_no=b.emp_no

order by growth

 

思路三：

问题描述：
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）

【要点】可能存在降薪的情况，所以最大工资-最小工资不一定是入职以来的涨幅。

可以通过连接现有表的方式进行

在员工信息表employees基础上，增加入职工资和当前工资

入职工资为s1.salary，条件为e.hire_date=s1.from_date;

当前工资为s2.salary，条件为s2.to_date='9999-01-01'

计算涨幅growth，为(s2.salary-s1.salary),并排序

1 2 3 4 5

select e.emp_no, (s2.salary-s1.salary) growth from employees e join salaries s1 on e.emp_no=s1.emp_no and e.hire_date=s1.from_date join salaries s2 on e.emp_no=s2.emp_no and s2.to_date='9999-01-01' order by growth

 思路四：

  select a.emp_no, (b.salary - a.salary) as growth

    from

    (

        select s.emp_no, s.salary

        from employees as e, salaries as s

        where e.emp_no = s.emp_no and e.hire_date = s.from_date

    ) as a,

    (

        select emp_no, salary

        from salaries

        where to_date = '9999-01-01'

    ) as b

    where a.emp_no = b.emp_no

    order by growth