可拷贝以下sql到查询分析器执行
(云社区拷贝sql格式可能有问题,可先拷贝到这里格式化后再执行:https://tool.oschina.net/codeformat/sql)
--查询3个月之内没有登录过的用户 (可替换sql后面的90,即90天)
select * from t_pm_user where fid in( SELECT fuserid FROM (select fuserid,fusername,max(foccurtime) as lastLoginTime from T_LOG_APP where fmethodalias_l2 ='用户登录' or fmethodalias_l2 = 'Portal登录' group by FUSERID,fusername) ss where DATEDIFF(dd, ss.lastLoginTime , GETDATE()) >= 90);
--备份用户表
select * into t_pm_user_bk220721 from t_pm_user;
--确认下是否备份成功
select * from t_pm_user_bk220721;
--将超过3个月未登录的用户禁用
update t_pm_user set fforbidden = 1 where fid in( SELECT fuserid FROM (select fuserid,fusername,max(foccurtime) as lastLoginTime from T_LOG_APP where fmethodalias_l2 ='用户登录' or fmethodalias_l2 = 'Portal登录' group by FUSERID,fusername) ss where DATEDIFF(dd, ss.lastLoginTime , GETDATE()) >= 90);
推荐阅读
您的鼓励与嘉奖将成为创作者们前进的动力,如果觉得本文还不错,可以给予作者创作打赏哦!
请选择打赏金币数 *