Placements(连接)

有三张表students(id, name), friends(id, friend_id),packages(id, salary)

要求输出:

1、输出朋友的工资比自己高的人名

2、按朋友的工资升序

思路：

1、friends与package表连接两次得到 id1, salary(id),salary(friend_id)

2、过滤到salary(friend_id)>salary(id)

3、与students连接

sql为

select students.name as name from
(
select t2.id1 as id1, t2.s1 as s1, t2.s2 as s2 from 
(
select t1.id1 as id1, t1.s1 as s1, p2.salary as s2 from 
(select f1.id as id1, f1.friend_id as id2, p1.salary as s1 from friends as f1 inner join packages p1 on f1.id = p1.id) t1
inner join packages p2 on t1.id2 = p2.id
) 
t2
where t2.s2 > t2.s1
) tmp inner join students on tmp.id1 = students.id
 order by tmp.s2;