在SQL Server 2008中按特定ID删除重复记录

问题描述

SELECT distinct  loc.name,
             loc.imageurl,
              tu.userid,
             tu.fname,
             (SELECT TOP(1) [time]
              FROM   tblcheckin
              WHERE  userid = tf.followerid  and flag ='C'
              ORDER  BY [time] DESC) AS [time]
           ,  CONVERT(DECIMAL(16, 2), (dbo.DistanceBetween(  loc.latitude,loc.longitude,22.302647,73.190144))) as Distance
              FROM   tbl_follower tf
             INNER JOIN tbluser tu
                     ON tf.followerid = tu.userid
             LEFT OUTER JOIN tblgetlocation loc
                          ON loc.venueid = (SELECT TOP(1) locationid
                                            FROM   tblcheckin
                                            WHERE  userid = tf.followerid and flag ='C'
                                            ORDER  BY [time] DESC)
      where   tf.userid = 57 and tf.flag='YES'

此处是我的输出


这是我上面的查询的输出，我已经应用了独特的查询，但是直到由于imageurl列而导致数据被重复..所以我如何使用userid从结果中删除重复的记录.如果重复用户名，则删除记录.

在我的输出中，您可以看到包含所有数据的记录取决于userid，但是我想删除具有相同userid的重复记录(如果它们大于1)，然后删除带有"ss1.4sql"的imageurl的相同userid的记录.网...''; –

Here My Output


Here is the output of my above query i have applied distinct but till the data are duplicated because of imageurl column.. so how can i remove duplicated record from result using userid. if userid repeated then delete record.

In my out put you can see,that the record with all data depends on userid, but i want to delete the record repeated with same userid if they are more then 1 then delete record of same userid having imageurl with ''ss1.4sql.net...''; –

推荐答案

DELETE
FROM TempCountry
WHERE CountryID NOT IN
	(
	SELECT MIN(CountryID)
	FROM TempCountry
	GROUP BY CountryCode
	)

...Your Query

AND loc.imageurl NOT LIKE '%ss1.4sql.net%'

这篇关于在SQL Server 2008中按特定ID删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！