MySQL 多个聚合函数和外连接

Question

我有两个问题:

第一:

select x.year, sum(x.cnt)
from (select count(id)*3 as cnt from takes where year = 2009 group by year) x
group by x.year;

我收到的错误是“字段列表中的未知列 x.year。我希望查询充当 selectyear, sum(count(id)*3);

第二:

我需要编写一个与以下内容相同的查询，无需外连接:

select instructor.id, name, count(teaches.id) as 'Number of Sections'
from instructor natural left outer join teaches
group by teaches.id, name, instructor.id
order by instructor.id asc;

我尝试了以下方法，但没有成功:

(select instructor.id, name, count(teaches.id) as 'Number of Sections'
from instructor, teaches
where instructor.id = teaches.id
group by teaches.id, name, instructor.id)
union
(select i.id, i.name, count(t.id)
from instructor i, teaches t
where i.id not in (select id from teaches)
group by t.id, i.name, i.id);

谢谢!

Answer 1

尝试下面的第一个

select x.year, sum(x.cnt)
from (select count(id)*3 as cnt, year from takes where year = 2009 group by year) x
group by x.year;