MySQL:子查询(基本)

Question

我是SQL的新手，正在尝试创建一个基本子查询。我需要找出经理的平均年龄和实习生的平均年龄之间的差异。

标题是一列-经理或实习生年龄是一列，都在同一个表中。

我是否可以使用两个子查询来做类似这样的事情：

Select manager_age - trainee_age
from book1
(select avg(age) as manager_age from book1 where title = "manager")

和另一个子查询：

(select avg(age) as manager_age from book1 where title = "trainee") 

我不确定如何在一个查询中做两个子查询，或者我是否应该用另一种方式来做这件事，比如连接？谢谢!

Answer 1

create table book1(title varchar(50), age integer) ;

insert into book1 values('Trainee', 10);
insert into book1 values('Trainee', 20);
insert into book1 values('Manager', 30);
insert into book1 values('Manager', 40);
insert into book1 values('Manager', 50);

select * from book1;

select m.m_age - t.t_age from (
(select title as m_title, avg(age) as m_age
 from book1 where title = 'Manager') m, 
(select title as t_title, avg(age) as t_age
 from book1 where title = 'Trainee') t);