解决数据库中记录重复问题(转)

最新推荐文章于 2024-05-28 14:48:01 发布

转载最新推荐文章于 2024-05-28 14:48:01 发布 · 554 阅读

文章标签：

#数据库 #python #移动开发

本文介绍了一种使用SQL语句进行数据去重的方法，包括产品数据、用户记录和自定义类别的去重处理，以及如何创建不包含重复记录的新表。通过分步查询和临时表操作，确保了数据的准确性和一致性。

--产品数据重复统计

SELECT mc, userid, COUNT(mc) AS Expr1
FROM chanpin
GROUP BY mc, userid
--将不重复的纪录插入新表newchanpin
select * into #Tmp1 from chanpin
go
select min(ID) as autoID into #Tmp2 from #Tmp1 group by mc, userid

go
select * into newchanpin from #Tmp1 where ID in(select autoID from #tmp2)

--查找重复用户
--select distinct name from user_name
select * into #Tmp0 from user_name
go
select min(ID) as autoID into #Tmp6 from #Tmp0 group by admin
go
select * into newuser_name from #Tmp0 where ID in(select autoID from #tmp6)

--用户自定义类别
SELECT userlb AS Expr1, userid AS Expr2, COUNT(userlb) AS Expr3
FROM newuser_lb
GROUP BY userlb, userid
select * into #Tmp8 from user_lb
go
select min(ID) as autoID into #Tmp9 from #Tmp8 group by userlb, userid
go
select * into newuser_lb from #Tmp8 where ID in(select autoID from #tmp9)
--用户新闻
select bt, userid,count(bt) from user_news group by bt,userid
select * into #Tmp88 from user_news
go
select min(ID) as autoID into #Tmp99 from #Tmp88 group by bt,userid
go
select * into newuser_news from #Tmp88 where ID in(select autoID from #tmp99)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7178747/viewspace-161525/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7178747/viewspace-161525/