如果是空值又怎么办?
上面的语句我是写在触发器中的
create or replace trigger a_lldd_Trigger
before insert on a_lldd
for each row
declare
pragma autonomous_transaction;
cnt number;
begin
cnt:=0;
select distinct b.dj into cnt from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh);
:new.dj:=cnt;
end;
上面的语句我是写在触发器中的
create or replace trigger a_lldd_Trigger
before insert on a_lldd
for each row
declare
pragma autonomous_transaction;
cnt number;
begin
cnt:=0;
select distinct b.dj into cnt from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh);
:new.dj:=cnt;
end;
select max(id), dj
from (select rownum id, distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729))如果求平均数怎么做?
不太懂,什么意思?
select max(id), dj
from (
select rownum id, dj
from (select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729)))
平均数
select avg(dj)
from (
from (select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729)))
select max(id), dj
from (
select rownum id, dj
from (select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729)))
这个语句出错。错误是ORA-00937:非单组分组函数
FROM (select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729)
)
WHERE ROWNUM = SELECT MAX(ID)
FROM (
SELECT ROWNUM ID DJ
FROM (
select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=13729)
)
)
:new.je:=new.sls * cnt;
你可以在update时直接 update 这个 :new.sls * cnt