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

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

解决数据库中记录重复问题 (By:aloxy)

Jul 22, 11:19

--产品数据重复统计

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)