现在有这么一个游戏角色表,里面保存的字段有UserID、ActorID、ActorLevel、Experience、WriteTime,
要求是每个UserID下只保留一个ActorID,且这个ActorID的ActorLevel是最大的,如果ActorLevel相同,则保留Experience最高的,否则保留WriteTime最早的。
我是这么写的:
DELETE dbo.GameActor FROM dbo.GameActor a
WHERE (
SELECT COUNT(ActorLevel)
FROM dbo.GameActor
WHERE (UserID=a.UserID AND ActorLevel>a.ActorLevel) OR (UserID=a.UserID AND ActorLevel=a.ActorLevel
AND Experience>a.Experience) OR (UserID=a.UserID AND ActorLevel=a.ActorLevel
AND Experience=a.Experience AND WriteTime>a.WriteTime)
)
)>1
这个的执行效率太低 谁能帮我改进下? 谢谢
------解决方案--------------------
- SQL code
DELETE dbo.GameActor FROM dbo.GameActor aWHERE EXISTS( SELECT 1 FROM dbo.GameActor WHERE (UserID=a.UserID AND ActorLevel>a.ActorLevel) OR (UserID=a.UserID AND ActorLevel=a.ActorLevel AND Experience>a.Experience) OR (UserID=a.UserID AND ActorLevel=a.ActorLevel AND Experience=a.Experience AND WriteTime>a.WriteTime) )
------解决方案--------------------
- SQL code
-->tryDELETE dbo.GameActor FROM dbo.GameActor aWHERE ( SELECT COUNT(ActorLevel) FROM dbo.GameActor WHERE UserID=a.UserID AND ((ActorLevel>a.ActorLevel) or (ActorLevel=a.ActorLevel AND Experience>a.Experience)) --OR (UserID=a.UserID AND ActorLevel=a.ActorLevel AND Experience=a.Experience AND WriteTime>a.WriteTime) )>1