INSERT INTO FillListTab(Phoneno,Fillym,Filldate,Fillmoney,
Remainmoney,Remainmonth,Fillstate,
Filledmoney,Filledmonths)
SELECT b.phoneno,
to_char(current_date,'YYYY')*100+to_char(current_date,'dd'),
sysdate,
FillDate,
a.Yhmonth,
Fillmoney,
a.Yhsum-b.Filledmoney-a.Yhmonth,
a.Monthsum-b.Filledmonths-1 ,
0 ,
b.Filledmoney+a.Yhmonth,
b.Filledmonths+1,
from CorrespondTab a,Filllisttab b
where a.Esnno=Tesnno,
and a.Phoneno=b.Phoneno
and b.Filldate = ( SELECT max(Filldate)
FROM Filllisttab c
WHERE a.Phoneno=c.Phoneno);
Remainmoney,Remainmonth,Fillstate,
Filledmoney,Filledmonths)
SELECT b.phoneno,
to_char(current_date,'YYYY')*100+to_char(current_date,'dd'),
sysdate,
FillDate,
a.Yhmonth,
Fillmoney,
a.Yhsum-b.Filledmoney-a.Yhmonth,
a.Monthsum-b.Filledmonths-1 ,
0 ,
b.Filledmoney+a.Yhmonth,
b.Filledmonths+1,
from CorrespondTab a,Filllisttab b
where a.Esnno=Tesnno,
and a.Phoneno=b.Phoneno
and b.Filldate = ( SELECT max(Filldate)
FROM Filllisttab c
WHERE a.Phoneno=c.Phoneno);
INSERT INTO FillListTab(Phoneno,Fillym,Filldate,Fillmoney,Remainmoney,Remainmonth,Fillstate,Filledmoney,Filledmonths)
select
a.phoneno,
to_char(current_date,'YYYY')*100+to_char(current_date,'mm'),
sysdate,
a.Yhmonth,
a.Yhsum-b.Filledmoney-a.Yhmonth,
a.Monthsum-b.Filledmonths-1,
0,
b.Filledmoney+a.Yhmonth,
b.Filledmonths+1
from CorrespondTab a,
Filllisttab b
where a.Esnno=Tesnno
and a.Phoneno=b.Phoneno
and b.Fillym=lastfillym;
错误提示说无效的列名啊。。所有类似与b.Filledmonths这样的都说是无效的列名啊
--------- ---------- ----------
02-FEB-05 1 10
02-FEB-05 1 20
02-FEB-05 1 20
02-FEB-05 1 20
02-FEB-05 2 50
02-FEB-05 2 506 rows selected.SQL> select * from tt2; C1 C2
---------- ----------
1 50
2 100SQL> insert into tt1
2 select sysdate,a.c2,b.c2
3 from tt1 a, tt2 b
4 where a.c2 = b.c1
5 and a.c1 = ( select min(c.c1) from tt1 c where a.c2 = c.c2)
6 /2 rows created.SQL> select * from tt1;C1 C2 C3
--------- ---------- ----------
02-FEB-05 1 10
02-FEB-05 1 20
02-FEB-05 1 20
02-FEB-05 1 20
02-FEB-05 2 50
02-FEB-05 2 50
02-FEB-05 1 50
02-FEB-05 2 1008 rows selected.SQL>