insert into empwork select case when sal > 8000 then sal - 400 when sal < 5000 then sal + 600 else sal end sal, ... from department d,emp e where d.depcode = e.depcode and e.[入职日期] >= '2010-01-01'
emp表与department表关联有什么意义?insert into empwork ( empno,empname,depcode,depname,sal,hiredate ) select a.empno, a.ename, a.deptno, b.dname, case when a.sal <5000 then a.sal+600 when a.sal >8000 then a.sal-400 end, a.hiredate from emp a,dept b where a.deptno = b.deptno and a.hiredate <= date '2010-1-1';
参考sql: insert into empwork ( empno,empname,depcode,depname,sal,hiredate ) select a.empno, a.ename, a.deptno, b.dname, case when a.sal <5000 then a.sal+600 when a.sal >8000 then a.sal-400 end, a.hiredate from emp a,dept b where a.deptno = b.deptno and a.hiredate <= to_date( '2010-01-01','YYYY-MM-DD') and not exists(select 1 from empwork w where w.empno=a.empno); 注意避免重复员工插入
create table empwork(empid varchar2(5),empname varchar2(8),salary number,deptname varchar2(20)) as (select a.empid,a.empname,a.salary,a.deptname from emp a,department b where a.deptid=b.deptid and rjdate<=to_date('20100101','yyyymmdd'))update empwork set salary=salary+600 where empid in(select empid from empwork where salary<5000)update empwork a set salary=salary-400 where exists(select 1 from empwork where a.empid=empid and salary>8000)
select
case when sal > 8000 then sal - 400 when sal < 5000 then sal + 600 else sal end sal,
...
from department d,emp e
where d.depcode = e.depcode
and e.[入职日期] >= '2010-01-01'
(
empno,empname,depcode,depname,sal,hiredate
)
select a.empno,
a.ename,
a.deptno,
b.dname,
case when a.sal <5000
then a.sal+600
when a.sal >8000
then a.sal-400
end,
a.hiredate
from emp a,dept b
where a.deptno = b.deptno
and a.hiredate <= date '2010-1-1';
insert into empwork
(
empno,empname,depcode,depname,sal,hiredate
)
select a.empno,
a.ename,
a.deptno,
b.dname,
case when a.sal <5000
then a.sal+600
when a.sal >8000
then a.sal-400
end,
a.hiredate
from emp a,dept b
where a.deptno = b.deptno
and a.hiredate <= to_date( '2010-01-01','YYYY-MM-DD')
and not exists(select 1 from empwork w where w.empno=a.empno);
注意避免重复员工插入
as
(select a.empid,a.empname,a.salary,a.deptname from emp a,department b where a.deptid=b.deptid and rjdate<=to_date('20100101','yyyymmdd'))update empwork
set salary=salary+600
where empid in(select empid from empwork where salary<5000)update empwork a
set salary=salary-400
where exists(select 1 from empwork where a.empid=empid and salary>8000)