set @id = 1; select a.*,b.max,b.min,c.dep_max,c.dep_min,pay - b.max,pay - b.min,pay - c.dep_max,pay - c.dep_min from (select e.id,e.name,e.dep,e.pay,d.depname from employee e,department d where e.dep = d.id and e.id = @id)a left join (select max(pay)max,min(pay)min,@id id from employee)b on a.id = b.id left join (select max(pay)dep_max,min(pay)dep_min,dep from employee group by dep)c on a.dep = c.dep
select a.*,b.max,b.min,c.dep_max,c.dep_min,pay - b.max,pay - b.min,pay - c.dep_max,pay - c.dep_min
from
(select e.id,e.name,e.dep,e.pay,d.depname from employee e,department d where e.dep = d.id and e.id = @id)a
left join
(select max(pay)max,min(pay)min,@id id from employee)b
on a.id = b.id
left join
(select max(pay)dep_max,min(pay)dep_min,dep from employee group by dep)c
on a.dep = c.dep