表名 t 字段 tname(财务指标) tv1(本年数据) tv2(上年数据) time(时间) ;
selec tname,tv1,tv2 from t where time='2007-12-31 ' l1
selec tname,tv1,tv2 from t where time='2008-12-31 ' l2
怎样在生成本年度数据的时候将将上年的本年数据自动天成到 上年数据;
谢谢
selec tname,tv1,tv2 from t where time='2007-12-31 ' l1
selec tname,tv1,tv2 from t where time='2008-12-31 ' l2
怎样在生成本年度数据的时候将将上年的本年数据自动天成到 上年数据;
谢谢
SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- --------- ---------- ----------
Khoo 18-MAY-95 3100 0
Tobias 24-JUL-97 2800 3100
Baida 24-DEC-97 2900 2800
Himuro 15-NOV-98 2600 2900
Colmenares 10-AUG-99 2500 2600
(tname, tv1, tv2, time)
values
(财务指标,
本年数据,
(select tv2 from t where time = '2007-12-31'),
'2008-12-31')
selec tname,tv1,tv2 from t where time='2007-12-31 ' t1 去年财务数据(多行,每行财务指标不同)
selec tname,tv1,tv2 from t where time='2008-12-31 ' t2 今年财务数据(多行,每行财务指标不同)
怎样在生成本年度数据的时候将将上年的t1.v1自动填充到 t2.tv2(多行);
set tv1 = (select tv2 from t where time = '2007-12-31')
where time = '2008-12-31'
set t1.tv1 = nvl((select t2.tv2
from t t2
where t2.time = '2007' || substr(t1.time, 5)),
0)
where sub(t1.time, 1, 4) = '2008'
写个触发器,用它的after insert事件CREATE OR REPLACE TRIGGER 触发器名
AFTER INSERT ON 你的表名
BEGIN
UPDATE SET TV2 = 上年的数据
WHERE 条件(定位到你刚才insert的那条数据);
END;
update t t1
set tv2 = (select tv1 from t2 where t12.name=t1.name and t2.time = '2007-12-31')
where time = '2008-12-31'