Question

数据库SQL高手请进，查找重复数据。

假如有表A（a0,a1,a2,a3)分别表示序号，姓名，科目，分数
a0 a1 a2 a3
1 张三 语文 80
2 李四 语文 82
3 张三 语文 80
4 王五 语文 92
5 张三 数学 65
6 李四 数学 88
7 张三 数学 65
8 王五 数学 90
9 二麻子 语文 86
10 二麻子 数学 89
我现在要1，查出姓名，科目，分数相同的记录，语句怎么写呢？
2，删出姓名，科目，分数相同的记录，语句怎么写呢？
3，查出同一个人，语文数学都超过80分，但是只能找出语文数学总分之后最小的那一个，语句 怎么写呢？

Answer 1

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 from A where a2 in (N'语文',N'数学') and a3 > 80
group by a1 having count(*) = 2 ) as b
order by ttl

Answer 2

1.select a1,a2,a3 from A group by a1,a2,a3 having count(*)>1 ;
2.create table bak as (select * from test group by title having count(*)=1);
insert into bak (select * from test group by title having count(*)>1);
truncate table test;
insert into test select * from bak;
drop table if exists bak ;

Answer 3

为简略，我把a0,a1,a2,a3用ID，A，B，C代替
1、select table_A.* from A table_A,A table_B
where table_A.A=table_B.A
and table_A.B=table_B.B
and table_A.C=table_B.C
and table_A.ID<>table_B.ID
--把表A用作两份，来比较姓名，科目，分数相同，但ID不同的记录

2、以1的查询为基础，找到姓名，科目，分数相同的最小记录，
只要跟这个最小ID不一样的，都删去就行了。
由于语句太长了，分步执行较清晰
先选出最小记录，存入临时表#tmp
select min(ID) as minID,A,B,C into #tmp from (select table_A.* from A table_A,A table_B
where table_A.A=table_B.A
and table_A.B=table_B.B
and table_A.C=table_B.C
and table_A.ID<>table_B.ID)table_C group by table_C.A,table_C.B,table_C.C
然后就可以删除其它相同记录了
delete from A where A.ID>(select minID from #tmp where A.A=#tmp.A and A.B=#tmp.B and A.C=#tmp.C)

3、选出语文数学都高于80分的同学，求个总分，排下序就行了
select top 1 stu.A,sum(stu.C)as Score from
(select * from A where A.A not in(select A.A from A where A.C<80)) stu
group by stu.A order by Score