在大的数据库应用中,经常因为各种原因遇到重复的记录,造成数据的冗余和维护上的不便。
1.用rowid方法
2.用group by方法
3.用distinct方法
1。用rowid方法
据据oracle带的rowid属性,进行判断,是否存在重复,语句如下:
查数据:
删数据:
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 astruncate table table1;
insert into table1 select * from table_new;
例子:
假如有表A(a0,a1,a2,a3)分别表示序号,姓名,科目,分数
a0
1
2
3
4
5
6
7
8
9
10
我现在要:
1,查出姓名,科目,分数相同的记录,语句怎么写呢?
2,删出姓名,科目,分数相同的记录,语句怎么写呢?
3,查出同一个人,语文数学都超过80分,但是只能找出语文数学总分之后最小的那一个,语句
最佳答案
1.查出姓名,科目,分数相同的记录
select * from A as out
where exists(select * from
(select a1,a2,a3 from A group by a1,a2,a3 having count(*) > 1) as b
where a1 = out.a1 and a2 = out.a2 and a3 = out.a3)
2. 删出姓名,科目,分数相同的记录(这里应该是重复的记录只保留一条吧)
delete from A
where exists(
select * from
(select a1,a2,a3,min(a0) as min_id from A as b
group by a1,a2,a3 having count(*) > 1) as c
where a1 = A.a1 and a2 = A.a2 and a3 = A.a3 and A.a0 > min_id)
3.查出同一个人,语文数学都超过80分,但是只能找出语文数学总分之后最小的那一个
select top 1 a1, ttl from
(select a1,sum(a3) as ttl
group by a1 having count(*) = 2 ) as b
order by ttl