2021-07-20 刷题 24、25、26题

SQL24 获取所有非manager员工当前的薪水情况

---

SQL 24 题目 SQL 24 题目 SQL 24 题目 SQL 24 题目 SQL 24 题目

解答：

先找到所有非manager员工emp_no,再内连接工资表和部门表即可

SELECT DE.dept_no,S.emp_no,S.salary FROM

salaries AS S

INNER JOIN dept_emp AS DE ON DE.emp_no = S.emp_no

WHERE  S.to_date = "9999-01-01" AND S.emp_no NOT IN

(

SELECT DM.emp_no FROM dept_manager AS DM

INNER JOIN dept_emp AS  DE ON DM.emp_no = DE.emp_no)

评论区看到一个有意思的解答：

SELECT

    b.dept_no,

    a.emp_no,

    d.salary

FROM

    employees a,

    dept_emp b,

    dept_manager c,

    salaries d

WHERE

    a.emp_no = b.emp_no

    AND b.dept_no = c.dept_no

    AND b.emp_no != c.emp_no

    AND a.emp_no = d.emp_no

---

SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息

---

SQL 25 题目 SQL 25 题目 SQL 25 题目 SQL 25 题目

解答：

思路：先做一个经理表m，然后做一个员工表w，最后对比这两个表的工资的结果

SELECT

w.emp_no,

m.emp_no AS manager_no,

w.salary AS emp_salary,

m.salary AS manager_salary

FROM

(

SELECT

s.emp_no,

s.salary,

de.dept_no

FROM

salaries AS s

INNER JOIN dept_emp AS de ON de.emp_no = s.emp_no

WHERE

s.emp_no IN ( SELECT dm.emp_no FROM dept_manager AS dm INNER JOIN dept_emp AS de ON de.emp_no = dm.emp_no ) AND s.to_date = "9999-01-01"

) AS m  -------经理表

INNER JOIN (

SELECT

s.emp_no,

s.salary,

de.dept_no

FROM

salaries AS s

INNER JOIN dept_emp AS de ON de.emp_no = s.emp_no

WHERE

s.emp_no NOT IN ( SELECT dm.emp_no FROM dept_manager AS dm INNER JOIN dept_emp AS de ON de.emp_no = dm.emp_no ) AND s.to_date = "9999-01-01"

) AS w   -------员工表

ON m.dept_no = w.dept_no

WHERE w.salary>m.salary   --------员工工资大于其经理工资

---

SQL26 汇总各个部门当前员工的title类型的分配数目

---

SQL 26 题目 SQL 26 题目 SQL 26 题目 SQL 26 题目

解答：

不知道为什么我的这个代码是错误的？

SELECT

de.dept_no,

d.dept_name,

t.title,

COUNT( t.title ) AS count

FROM

titles AS t

INNER JOIN dept_emp AS de ON t.emp_no = de.emp_no

INNER JOIN departments AS d ON d.dept_no = de.dept_no

WHERE de.to_date = "9999-01-01" AND t.to_date = "9999-01-01"

GROUP BY

de.dept_no,

d.dept_name,

t.title

讨论区答案：

select c.dept_no

      ,c.dept_name

      ,d.title

      ,count(d.title)

from titles d

inner join

          (select a.dept_no

                  ,a.emp_no

                  ,b.dept_name

                  ,a.from_date

                  ,a.to_date

            from dept_emp a

            inner join departments b

            on a.dept_no=b.dept_no) c

on d.emp_no=c.emp_no

where c.to_date='9999-01-01' 

group by c.dept_no,d.title

order by c.dept_no asc