22,210
社区成员
发帖
与我相关
我的任务
分享
-先更新 玩家表
UPDATE 玩家表
SET mailBox=ISNULL(
(SELECT STUFF((','+CAST(T4.id AS VARCHAR)),1,1,'')
FROM(
SELECT T1.uid,T1.mailBox
FROM 玩家表 T1
JOIN(SELECT id FROM 邮箱表 WHERE createTime>100)T2
ON ','+T1.mailBox+','LIKE '%,'+CAST(T2.id AS VARCHAR)+',%'
GROUP BY T1.uid,T1.mailBox
)T3 JOIN(SELECT id FROM 邮箱表 WHERE createTime<=100)T4
ON ','+T3.mailBox+','LIKE '%,'+CAST(T4.id AS VARCHAR)+',%'
WHERE 玩家表.uid=T3.uid),'')
--删除邮箱表createTime>100的
DELETE 邮箱表 WHERE createTime>100
--如果你愿意多条语句处理,可能会更方便
--你的操作涉及到修改两个表,需要至少两条语句
--先更新 玩家表
UPDATE 玩家表
SET mailBox=ISNULL(
(SELECT STUFF((','+CAST(T4.id AS VARCHAR)),1,1,'')
FROM(
SELECT T1.uid,T1.mailBox
FROM 玩家表 T1
JOIN(SELECT id FROM 邮箱表 WHERE createTime>100)T2
ON ','+T1.mailBox+','LIKE '%,'+T2.id+',%'
GROUP BY T1.uid,T1.mailBox
)T3 JOIN(SELECT id FROM 邮箱表 WHERE createTime<=100)T4
ON ','+T3.mailBox+','LIKE '%,'+T4.id+',%'
WHERE 玩家表.uid=T3.uid),'')
--删除邮箱表createTime>100的
DELETE 邮箱表 WHERE createTime>100
--如果你愿意多条语句处理,可能会更方便