子查询 (SELECT e.empid,w.worktime FROM employee e INNER JOIN work w ON e.empid=w.empid INNER JOIN department d ON d.deptno=w.deptno WHERE d.dname='财务' AND w.worktime>3)中多select了一个字段w.worktime
你造的数据有问题 work 表中同一个人有不同的部门和工作年限,请注意! UPDATE employee e SET sal = case when (select w.worktime from work w where e.empid = w.empid) < 3 then sal * (1 + 0.03) when (select w.worktime from work w where e.empid = w.empid) >= 3 then sal * (1 + 0.05) end WHERE exists (SELECT 1 FROM work w, department d WHERE e.empid = w.empid and d.deptno = w.deptno and d.dname = '财务');
select t.empid, t.empname, t.age, decode (ta.worktime,2,(1+0.03)*t.sal,1,(1+0.03)*t.sal,0,(1+0.03)*t.sal,(1+0.05)*t.sal)newsal from employee t,work ta,department tb where t.empid=ta.empid and ta.deptno=tb.deptno and tb.deptno=4
(SELECT e.empid,w.worktime FROM employee e INNER JOIN work w ON
e.empid=w.empid INNER JOIN department d ON d.deptno=w.deptno
WHERE d.dname='财务' AND w.worktime>3)中多select了一个字段w.worktime
那么,如果用PL/SQL语句来实现的话,怎么写呢??
work 表中同一个人有不同的部门和工作年限,请注意!
UPDATE employee e
SET sal = case
when (select w.worktime from work w where e.empid = w.empid) < 3 then
sal * (1 + 0.03)
when (select w.worktime from work w where e.empid = w.empid) >= 3 then
sal * (1 + 0.05)
end
WHERE exists (SELECT 1
FROM work w, department d
WHERE e.empid = w.empid
and d.deptno = w.deptno
and d.dname = '财务');
from employee t,work ta,department tb
where t.empid=ta.empid
and ta.deptno=tb.deptno
and tb.deptno=4
都说了你work表里的数据不符合实际情况,有问题数据