Question

SQL 中两张表合并后，如何删除重复项？

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”的数据，怎么去除另外一条重复的？

Answer 1

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)

Answer 2

如果你非要查询出你要查询的那些字段的话，不能去掉重复，因为你的一个ID对应了两个不同的Answer,在查询时就查出两条一样的了，不然去掉一条重复的，那你说去掉Answer为nice的那条 数据，还是cool的那条数据？如果你想要这样去掉的话直接加个条件Answer=''去掉的那条就可以了

Answer 3

2张表结构如果完全一致
合并前可以使用union语句联合去重
select * from t1 union select * from t2
然后插入

Answer 4

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