解答：
先找到所有非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当前薪水还高的相关信息
解答：
思路：先做一个经理表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 --------员工工资大于其经理工资
解答：
不知道为什么我的这个代码是错误的？
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
