sql 删除重复记录语句收藏
sql 删除重复记录语句收藏
删除重复记录的题目。现给出解决此题的答案，如下:
select distinct * into Temp from Student
truncate table Student
insert Student select * from Temp
drop table Temp
--至此删除了重复的数据。
--显示删除后的结果
select * from student
思路是先查询出没有重复的数据保存到一个临时的表Temp里面，然后再用truncate关键字删除目标表的数据内容，最后使用drop命令删除Temp表即可。
--------------------------------------------------------------------------------
斐波那契数列非递归的C#代码解法如下：用数学里面的通项
static Int64 Fib(Int64 n)
{
int first, second, third;
first = second = third = 1;
if (n==1||n==2)
{
return 1;
}
for (int i = 3; i <= n; i++)
{
first = second + third;
third = second;
second = first;
}
return first;
}
Fibonacci（斐波那契）序列：
Fib(n) = Fib(n - 1) + Fib(n - 2), n>1, Fib(1) = Fib(2) = 1
即：序列的第一和第二项是1，从第三项开始，后一项是前两项的和。
序列的前8项是：
1, 1, 2, 3, 5, 8, 13, 21
更多更好方法
若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来
select p1.* from persons p1,persons p2 where p1.id<>p2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address
可以实现上述效果．
几个删除重复记录的SQL语句
1.用rowid方法
2.用group by方法
3.用distinct方法
1。用rowid方法
据据oracle带的rowid属性，进行判断，是否存在重复,语句如下：
查数据:
select * from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2......)
删数据：
delete from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2......)
2.group by方法
查数据:
select count(num), max(name) from student --列出重复的记录数，并列出他的name属性
group by num
having count(num) >1 --按num分组后找出表中num列重复，即出现次数大于一次
删数据：
delete from student
group by num
having count(num) >1
这样的话就把所有重复的都删除了。
3.用distinct方法 -对于小的表比较有用
create table table_new as select distinct * from table1 minux
truncate table table1;
insert into table1 select * from table_new;
查询及删除重复记录的方法大全
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where 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)
3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (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 a
where (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)
补充：数据库,Mssql
