H在数据库中是不存在的,而是一个庞大的代码(select decode(sign(E.B),-1,0,
decode(sign(E.B-500),-1,E.B*0.05,
decode(sign(E.B-2000),-1,E.B*0.1-25,
decode(sign(E.B-5000),-1,E.B*0.15-125,
decode(sign(E.B-20000),-1,E.B*0.20-375,
decode(sign(E.B-40000),-1,E.B*0.25-1375,
decode(sign(E.B-60000),-1,E.B*0.30-3375,
decode(sign(E.B-80000),-1,E.B*0.35-6375,
decode(sign(E.B-100000),-1,E.B*0.40-10375,
B*0.45-15375
))))))))) F,E.C G
from
(select (sum1+sum2+sum3+sum4+sum6-800) B,p1.emp_id C from
(select emp_id,sum(amount) sum1 from
payment_item where item_id in (0101,0102,0103,0104,0148)
and to_char(pay_date,'yyyy-mm')='2002-12' group by emp_id)p1,
(select emp_id,sum(amount)-decode(emp_id,4,500,
156,500,186,500,190,500,526,500,626,500,300) sum2
from payment_item where item_id in (0301,0308,0309)
and to_char(pay_date,'yyyy-mm')='2002-12' group by emp_id)p2,
(select emp_id,sum(amount) sum3 from payment_item
where item_id in (0110,0111,0206)
and to_char(pay_date,'yyyy-mm')='2002-12'
group by emp_id)p3,
(select emp_id,amount sum4 from payment_item
where item_id in (9999) and pay_id=2
and to_char(pay_date,'yyyy-mm')='2002-12')p4,
(select emp_id,sum(amount) sum6 from payment_item
where item_id in (0152,0153,0154)
and to_char(pay_date,'yyyy-mm')='2002-12'
group by emp_id)p6
where p1.emp_id=p2.emp_id
and p1.emp_id=p3.emp_id
and p1.emp_id=p4.emp_id
and p1.emp_id=p6.emp_id)E)H
decode(sign(E.B-500),-1,E.B*0.05,
decode(sign(E.B-2000),-1,E.B*0.1-25,
decode(sign(E.B-5000),-1,E.B*0.15-125,
decode(sign(E.B-20000),-1,E.B*0.20-375,
decode(sign(E.B-40000),-1,E.B*0.25-1375,
decode(sign(E.B-60000),-1,E.B*0.30-3375,
decode(sign(E.B-80000),-1,E.B*0.35-6375,
decode(sign(E.B-100000),-1,E.B*0.40-10375,
B*0.45-15375
))))))))) F,E.C G
from
(select (sum1+sum2+sum3+sum4+sum6-800) B,p1.emp_id C from
(select emp_id,sum(amount) sum1 from
payment_item where item_id in (0101,0102,0103,0104,0148)
and to_char(pay_date,'yyyy-mm')='2002-12' group by emp_id)p1,
(select emp_id,sum(amount)-decode(emp_id,4,500,
156,500,186,500,190,500,526,500,626,500,300) sum2
from payment_item where item_id in (0301,0308,0309)
and to_char(pay_date,'yyyy-mm')='2002-12' group by emp_id)p2,
(select emp_id,sum(amount) sum3 from payment_item
where item_id in (0110,0111,0206)
and to_char(pay_date,'yyyy-mm')='2002-12'
group by emp_id)p3,
(select emp_id,amount sum4 from payment_item
where item_id in (9999) and pay_id=2
and to_char(pay_date,'yyyy-mm')='2002-12')p4,
(select emp_id,sum(amount) sum6 from payment_item
where item_id in (0152,0153,0154)
and to_char(pay_date,'yyyy-mm')='2002-12'
group by emp_id)p6
where p1.emp_id=p2.emp_id
and p1.emp_id=p3.emp_id
and p1.emp_id=p4.emp_id
and p1.emp_id=p6.emp_id)E)H
第一个where A.emp_id=H.G 应该已经限制住了吧。如果还不行那就得分两步来做了,否则就得建视图了。
(select H.F from H
where A.emp_id=H.G
)
where
A.item_id='9999'
and A.pay_id=11
A.item_id='9999'
and A.pay_id=11
A表的主键由A.item_id,A.pay_id,A.emp_id构成SQL> desc payroll_item;
名称 是否为空?类型
------------------------------- -------- ----
PAY_ID NOT NULL NUMBER(2)
EMP_ID NOT NULL NUMBER(4)
ITEM_ID NOT NULL CHAR(4)
AMOUNT NUMBER(12,2)这还不行,还有很多记录
要update唯一一条记录还要加A.emp_id=H.G
自动计算公式,视图能随时更新记录吗?打开一个emp_id职工,这个月的个人调节税item_id=0157就能自动计算显示出来
个人调节税属于pay_id大类的
这点是肯定的吧?
有解决方案吗?
是这样的吗?
create table tablename as select * from H
我觉得还是视图好,可以随时更新数据
不知我所说的对不对?
名称 是否为空?类型
------------------------------- -------- ----
PERSONAL_TAX NUMBER
EMP_ID NOT NULL NUMBER(4)SQL> desc payroll_item;//是张表
名称 是否为空?类型
------------------------------- -------- ----
PAY_ID NOT NULL NUMBER(2)
EMP_ID NOT NULL NUMBER(4)
ITEM_ID NOT NULL CHAR(4)
AMOUNT NUMBER(12,2)
SQL> update payroll_item
2 set amount=(
3 select personal_tax
4 from test3
5 where test3.emp_id=payroll_item.emp_id)
6 where test3.emp_id=payroll_item.emp_id
7 and payroll_item.item_id='0157'
8 and payroll_item.pay_id=11;
where test3.emp_id=payroll_item.emp_id
*
ERROR 位于第 6 行:
ORA-00904: invalid column name
为什么会报这个错误》??
原因何在???
set (emp_id,amount) = (select emp_id,personal_tax
from test3 b
where b.emp_id = a.emp_id)
where a.item_id='0157'
and a.pay_id=11;
或者:update (select a.emp_id aeid,
a.item_id aiid,
a.pay_id apid,
a.amount aat,
b.emp_it beid,
b.personal_tax bpt
from payroll_item a,test3 b
where a.item_id='0157'
and a.pay_id=11)
set aat = bpt第二种方法如果test3中没有主键的话,则不可以。
你可以参考一下:
http://www.itpub.net/showthread.php?s=83de6bcdef24f1fa570399103e3e8562&threadid=45822&perpage=15&pagenumber=1
名称 是否为空?类型
------------------------------- -------- ----
PERSONAL_TAX NUMBER
EMP_ID NOT NULL NUMBER(4)SQL> desc payroll_item;//是张表
名称 是否为空?类型
------------------------------- -------- ----
PAY_ID NOT NULL NUMBER(2)
EMP_ID NOT NULL NUMBER(4)
ITEM_ID NOT NULL CHAR(4)
AMOUNT NUMBER(12,2)
数据类型不一样会影响update的,
AMOUNT NUMBER(12,2)
EMP_ID NOT NULL NUMBER(4)