最近赶个项目,里面要统计末末的属性
<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:ËÎÌå; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-alt:"Times New Roman"; mso-font-charset:0; mso-generic-font-family:auto; mso-font-format:other; mso-font-pitch:auto; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体; mso-font-kerning:1.0pt;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.Section1 {page:Section1;} -->
for LASTLOGIN_cur in ( select username,last_landtime from a where landtime > trunc( sysdate ) - 7 )
loop
update a
set (last_landtime,last_account,update_time)=
(
select LASTTIME,USERMONEY, sysdate
from b
where f_username = LASTLOGIN_cur.f_username
and LASTTIME > ASTLOGIN_cur.f_last_landtime
) where a.f_username = LASTLOGIN_cur.f_username ;
for cur in(....)
loop
for cur in(....)
像上面第一条 把所有的查询写到IN里面去 获得运算后的结果,然后循环更新。
下面语句 也是把所有的 写到IN 中 采用LEFT JOIN 来 排除不存在的数据 再来运算
for n_fill_curA in
(
select p. username,p. optime
from
(
select *
from
(
select username, optime,ROW_NUMBER() OVER (PARTITION BY username ORDER BY optime asc) rowrid
from user_pay_his
where busisort = 20 and optime > trunc(sysdate)-7
) where rowrid =1
)p inner join
(
select username
from user_attr
where filltime is null
)b on p. username=b. username
)
loop
update t_behavior_attribute a --用户首次充值时间
set filltime = n_fill_curA. optime,
update_time = sysdate
where a. username = n_fill_curA. username
and a. filltime is null;
v_count :=v_count+1;
v_rownum :=v_rownum+sql%rowcount;
if mod(v_count,100)=0 then
v_count:=0;
commit;
end if;
end loop;
commit;