mysql - MySQL过去试卷中的select语句问题

Question

我翻阅了过去的一篇论文，发现了一些我不能做的选择语句。任何人都可以帮助解决这些问题吗？我正在使用 MySQL。谢谢

架构：

employee = (employee id, name, address, date of birth, salary)
project = (project id, name, budget, start date, end date)
manages = (employee id, project id)
works on = (employee id, project id)

问题 ：

1. 从事当前项目（尚未结束）的所有员工的姓名和地址

2. 项目经理的姓名，按他们管理的员工总数排序

3. 从事预算金额最大的项目的员工的姓名。

我的尝试：

1. 
select employee.name, address
from employee natural join works_on natural join project
where end_date is null

2. 
select employee.name, count(works_on.employee_id) as manages_count
from (employee natural join manages) joins works_on using (employee_id)


I'm completely lost I cannot even attempt this one ^


3. 
select employee.name, address, budget
from employee natural join works_on natural join project
order by budget
limit n

^ I know this is wrong as technically I should be able to show them without a limit

标签： mysqlsqlmariadb

Answer 1

干得好：

1.

select
  e.name, 
  e.address
from employee e
join works_on w on w.employee_id = r.employee_id
join project p on p.project_id = e.project_id
where p.end_date is null

2.

select 
  n.name
from employee n
join manages m on m.employee_id = n.employee_id
join works_on w on e.project_id = m.project_id
group by n.employee_id
order by count(*) desc

3.

select 
  e.name
from employee e
join works_on w on w.employee_id = e.employee_id
join project p on p.project_id = w.employee_id
where p.project_id in (
  select project_id from project where budget = (
    select max(budget) from project
  )
)