SQL 中两张表合并后,如何删除重复项?
selecta.ID,a.QuestionCategoryName,a.EmpID,a.RegDate,b.Question,b.Answerfromcrm_Questi...
select a.ID,a.QuestionCategoryName,a.EmpID,a.RegDate,b.Question,b.Answer from crm_QuestionCategory a left join dbo.crm_QAList b on a.ID=b.QuestionCategoryID where a.ID=2
只想要一条 “tour”的数据,怎么去除另外一条重复的? 展开
只想要一条 “tour”的数据,怎么去除另外一条重复的? 展开
展开全部
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)注:rowid为oracle自带不用该.....
3、查找表中多余的重复记录(多个字段) select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) www.2cto.com 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录delete from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
3、查找表中多余的重复记录(多个字段) select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) www.2cto.com 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录delete from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
展开全部
如果你非要查询出你要查询的那些字段的话,不能去掉重复,因为你的一个ID对应了两个不同的Answer,在查询时就查出两条一样的了,不然去掉一条重复的,那你说去掉Answer为nice的那条 数据,还是cool的那条数据?如果你想要这样去掉的话直接加个条件Answer=''去掉的那条就可以了
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
2张表结构如果完全一致
合并前可以使用union语句联合去重
select * from t1 union select * from t2
然后插入
合并前可以使用union语句联合去重
select * from t1 union select * from t2
然后插入
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
select a.ID,a.QuestionCategoryName,a.EmpID,a.RegDate,b.Question,max(b.Answer)
from crm_QuestionCategory a left join dbo.crm_QAList b on a.ID=b.QuestionCategoryID where a.ID=2
group by a.ID,a.QuestionCategoryName,a.EmpID,a.RegDate,b.Question
from crm_QuestionCategory a left join dbo.crm_QAList b on a.ID=b.QuestionCategoryID where a.ID=2
group by a.ID,a.QuestionCategoryName,a.EmpID,a.RegDate,b.Question
本回答被提问者采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询