Table1 A_PERNOTE VER_ID NOTE_NO NOTE_CODE
2229 0001 05
2229 0002 05
. . .
. . .
2229 0100 05Table2 A_NOTE_LOG
OPER_ID VER_ID BEG_NO END_NO OPER_CODE REMNUM
100049 2229 0001 0100 03 0根据Table1的NOTE_CODE 和同一状态下的票据数量更新Table2表中的OPER_CODE 和REMNUM字段
Table1记录每张票据的状态,Table2记录的是票据的日志,按段记录日志。使用VER_ID关联 ,OPER_ID为主键
select a.oper_id,decode(b.note_code,'04','11','05','12','06','13') as newopercode,
b.notenum
from A_NOTE_LOG a,
(select count(note_no) as notenum,
min(note_no) as minoteno,
max(note_no) as manoteno,
note_code,ver_id
from A_PERNOTE
where note_code = '05'
group by note_code,ver_id) b
where a.ver_id=b.ver_id
and a.remnum = 0
and a.oper_code = '03'
and a.bgn_no=b.minoteno
and a.end_no=b.manoteno 上面是查询语句,将要更新的值都查询出来了,newopercode,notenum 如何更新Table2 A_NOTE_LOG ?
2229 0001 05
2229 0002 05
. . .
. . .
2229 0100 05Table2 A_NOTE_LOG
OPER_ID VER_ID BEG_NO END_NO OPER_CODE REMNUM
100049 2229 0001 0100 03 0根据Table1的NOTE_CODE 和同一状态下的票据数量更新Table2表中的OPER_CODE 和REMNUM字段
Table1记录每张票据的状态,Table2记录的是票据的日志,按段记录日志。使用VER_ID关联 ,OPER_ID为主键
select a.oper_id,decode(b.note_code,'04','11','05','12','06','13') as newopercode,
b.notenum
from A_NOTE_LOG a,
(select count(note_no) as notenum,
min(note_no) as minoteno,
max(note_no) as manoteno,
note_code,ver_id
from A_PERNOTE
where note_code = '05'
group by note_code,ver_id) b
where a.ver_id=b.ver_id
and a.remnum = 0
and a.oper_code = '03'
and a.bgn_no=b.minoteno
and a.end_no=b.manoteno 上面是查询语句,将要更新的值都查询出来了,newopercode,notenum 如何更新Table2 A_NOTE_LOG ?
(select decode(b.note_code,'04','11','05','12','06','13'),
b.notenum
from(select count(note_no) as notenum,
min(note_no) as minoteno,
max(note_no) as manoteno,
note_code,ver_id
from A_PERNOTE
where note_code = '05'
group by note_code,ver_id) b
where a.ver_id=b.ver_id
and a.remnum = 0
and a.oper_code = '03'
and a.bgn_no=b.minoteno
and a.end_no=b.manoteno
)
where exists (select 1 from(select count(note_no) as notenum,
min(note_no) as minoteno,
max(note_no) as manoteno,
note_code,ver_id
from A_PERNOTE
where note_code = '05'
group by note_code,ver_id) b
where a.ver_id=b.ver_id
and a.remnum = 0
and a.oper_code = '03'
and a.bgn_no=b.minoteno
and a.end_no=b.manoteno
);