日期:2014-05-17  浏览次数:20553 次

SQL server 去掉重复字段
现在有这么一个游戏角色表,里面保存的字段有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 a
WHERE 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

-->try
DELETE dbo.GameActor FROM dbo.GameActor a
WHERE ( 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