select s_id, e_id, e_salary, avg_salary
from emp a, (select s_id, avg(e_salary avg_salary) from emp group by s_id) b
where a.s_id=b.s_id
and a.e_salary<b.avg_salary
from emp a, (select s_id, avg(e_salary avg_salary) from emp group by s_id) b
where a.s_id=b.s_id
and a.e_salary<b.avg_salary
这不就OK了吗?
ERROR 位于第 2 行:
ORA-00907: 缺少右括号
还有我不是很了解:
....avg(e_salary avg_salary).....是什么意思?beckhambobo(beckham)
你写的也不行,运行时出现错误:
ERROR 位于第 1 行:
ORA-00934: 此处不允许使用分组函数
where e_salary <avg(e_salary) over(partition by s_id order by rownum)
在SQL中e_salary <avg(e_salary)是不行的,你怎么能把查询结果直接作为查询条件呢
还有其中over(partition by s_id order by rownum)我看不懂,请解释下!
select * from emp where e_salary=avg(e_salary);
这样绝对不行,不信你自己试下
where a.salary <(select avg(b.salary )from emp b where a.s_id = b.s_id );
ERROR 位于第 2 行:
ORA-00907: 缺少右括号
还有我不是很了解:
....avg(e_salary avg_salary).....是什么意思?括弧括错了地方,呵呵
avg(e_salary) avg_salary
select a.s_id, a.e_id, a.e_salary
from emp a, (select s_id, avg(e_salary) avg_salary from emp group by s_id) b
where a.s_id=b.s_id
and a.e_salary<b.avg_salary
结果:
S_ID E_ID E_SALARY
-------- -------- ----------
wh1 e3 1210
wh2 e1 1220
可比较qfsb_p(我心飞翔):
select a,b from t1,(select avg(b)c from t1)t2 where t1.b<t2.c
结果:
S_ID E_ID E_SALARY
-------- -------- ----------
wh2 e1 1220
wh1 e3 1210
wh3 e6 1230
你的SQL语句结果刚好把wh3的记录去掉那,因为wh3仓库职工平均工资1230(不大于1230)!正确,能教教我写SQL语句吗?