有3个表,分别是useinfo用户表,login登录日志,daylog每日登录统计表userinfo表ID username pwd
1 aaa aaa
2 bbb bbb
3 ccc ccc
……login表
ID username logintime
1 aaa 2004-04-12
2 bbb 2004-04-12
3 aaa 2004-04-12
4 ccc 2004-04-12
5 aaa 2004-04-12
5 aaa 2004-04-13
……要实现daylog表ID username number logintime
1 aaa 3 2004-04-12
2 bbb 1 2004-04-12
3 ccc 1 2004-04-12
4 aaa 1 2004-04-13
……
每天0点以后将login表中的登录日志按userinfo表中的用户进行统计,将记录插入到daylog表中,请问这样的过程怎么写啊??
1 aaa aaa
2 bbb bbb
3 ccc ccc
……login表
ID username logintime
1 aaa 2004-04-12
2 bbb 2004-04-12
3 aaa 2004-04-12
4 ccc 2004-04-12
5 aaa 2004-04-12
5 aaa 2004-04-13
……要实现daylog表ID username number logintime
1 aaa 3 2004-04-12
2 bbb 1 2004-04-12
3 ccc 1 2004-04-12
4 aaa 1 2004-04-13
……
每天0点以后将login表中的登录日志按userinfo表中的用户进行统计,将记录插入到daylog表中,请问这样的过程怎么写啊??
as
begin
insert into daylog select id,username,count(1),logintime from login group by id,username,logintime;
delete from login;
commit;
end;
/
declare
jobno number;
begin
dbms_job.submit(jobno,'pro;',sysdate,'trunc(sysdate)+1');
commit;
end;
/