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);