--先更新已有的用户的访问统计数据:
update 用户表 set access_num = 用户表.access_num + tmp.access_num
from (select user_guid ,count(id) as access_num from 采样表 group by user_guid) as tmp
where tmp.user_guid=用户表.user_guid ;--再插入采样表中没被记录的用户数据:
insert into 用户表(user_guid , user_ua , user_ip ,createtime , access_num)
select user_guid ,min(user_ua),min(user_ip),min(createtime),count(id) from 采样表 group by user_guid ;--看看是否符合你的要求,效率应该比用cursor要高多
update 用户表 set access_num = 用户表.access_num + tmp.access_num
from (select user_guid ,count(id) as access_num from 采样表 group by user_guid) as tmp
where tmp.user_guid=用户表.user_guid ;--再插入采样表中没被记录的用户数据:
insert into 用户表(user_guid , user_ua , user_ip ,createtime , access_num)
select user_guid ,min(user_ua),min(user_ip),min(createtime),count(id) from 采样表 group by user_guid ;--看看是否符合你的要求,效率应该比用cursor要高多
set a.access_num=a.access_num+b.access_count
from 用户表 a join (select user_id,count(*) as access_count from 访问表 group by user_id ) b
on a.user_id=b.user_id2)插入没有记录的表insert into 用户表 values(user_id,user_ua,user_ip,create_time,access_num)select user_id,user_ua,user_ip,min(createtime),count(*) as access_count
from 访问表 a
where not exists
(select 1 from 用户表 b where
on a.user_id=b.user_id
)
group by user_id,user_ua,user_ip