select avg(a.sal)-avg(b.sal) from emp a,emp b where a.job='MANAGER' and b.job='SALESMAN';
select a.msal - b.ssal from( (select avg(sal) msal from emp where job = 'MANAGER')a (select avg(sal) ssal from emp where job = 'SALESMAN')b );
参考: select b.empname,b.salary - a.avg_sal from (select avg(salary) avg_sal from emp where job='MANAGER') a, (select * from emp where job='MANAGER') b
sorryselect a.msal - b.ssal from (select avg(sal) msal from emp where job = 'MANAGER') a, (select avg(sal) ssal from emp where job = 'SALESMAN')b ;
select a.msal - b.ssal from (select avg(sal) msal from emp where job = 'MANAGER') a, (select avg(sal) ssal from emp where job = 'SALESMAN')b
SELECT (SELECT AVG(sal) msal FROM emp WHERE job = 'MANAGER') - (SELECT AVG(sal) ssal FROM emp WHERE job = 'SALESMAN') FROM dual;
select max(sal) -min(sal) from select agv(sal) as sal from emp where job in('MANAGER','SALESMAN') group by job) a;
这样: select max(sal) -min(sal) from (select avg(sal) as sal from emp where job in('MANAGER','SALESMAN') group by job); 很好的方法
select avg(decode(t.job,'MANAGER',t.sal,null)) - avg(decode(t.job,'SALESMAN',t.sal,null)) from emp t where t.job in ('MANAGER','SALESMAN')
(1) select (select avg(sal) from emp where job = 'MANAGER') - (select avg(sal) from emp where job = 'SALESMAN') from dual (2) select max(sal) - min(sal) from (select avg(sal) as sal from emp where job in ('MANAGER', 'SALESMAN') group by job);
引用六楼: SELECT (SELECT AVG(sal) msal FROM emp WHERE job = 'MANAGER') - (SELECT AVG(sal) ssal FROM emp WHERE job = 'SALESMAN') FROM dual;
where a.job='MANAGER' and b.job='SALESMAN';
(select avg(sal) msal from emp where job = 'MANAGER')a
(select avg(sal) ssal from emp where job = 'SALESMAN')b
);
select b.empname,b.salary - a.avg_sal
from
(select avg(salary) avg_sal from emp where job='MANAGER') a,
(select * from emp where job='MANAGER') b
(select avg(sal) msal from emp where job = 'MANAGER') a,
(select avg(sal) ssal from emp where job = 'SALESMAN')b
;
(select avg(sal) msal from emp where job = 'MANAGER') a,
(select avg(sal) ssal from emp where job = 'SALESMAN')b
(SELECT AVG(sal) ssal FROM emp WHERE job = 'SALESMAN')
FROM dual;
select agv(sal) as sal from emp where job in('MANAGER','SALESMAN') group by job) a;
select max(sal) -min(sal) from
(select avg(sal) as sal from emp where job in('MANAGER','SALESMAN') group by job);
很好的方法
avg(decode(t.job,'SALESMAN',t.sal,null))
from emp t
where t.job in ('MANAGER','SALESMAN')
select (select avg(sal) from emp where job = 'MANAGER') -
(select avg(sal) from emp where job = 'SALESMAN')
from dual
(2)
select max(sal) - min(sal)
from (select avg(sal) as sal
from emp
where job in ('MANAGER', 'SALESMAN')
group by job);
SELECT (SELECT AVG(sal) msal FROM emp WHERE job = 'MANAGER') -
(SELECT AVG(sal) ssal FROM emp WHERE job = 'SALESMAN')
FROM dual;