请问，怎么样取得两个字段都重复的记录

[kill_jp]

我想取得表中a_class,a_name两个字段都重复的记录
例如取得如下的记录：a_id是自增值

a_id,a_class,a_name
1 1 111
2 1 111


像上面的两条记录，a_class,a_name两个字段都重复，怎样取得？
多谢各位大侠！不胜感激！！！

[ATGC]

select a_class,a_name
from table
group by a_class,a_name
having count(*)>1

[NinGoo]

select a.* from t1 a,t1 b
where a.a_id<>b.a_id and a.a_class=b.a_class and a.a_name=b.a_name
order by a.a_id

[NinGoo]

--测试数据
a_id a_class a_name
----------- ----------- -----------
1 1 111
2 1 111
3 1 112


--测试结果
a_id a_class a_name
----------- ----------- -----------
1 1 111
2 1 111

（所影响的行数为 2 行）

[frenchletter]

select a_class,a_name
from table
group by a_class,a_name
having count(*)>1

这个显然好得多
至少我一看就明白

[NinGoo]

select a_class,a_name
from table
group by a_class,a_name
having count(*)>1

－结果

a_class a_name
----------- -----------
1 111

（所影响的行数为 1 行）


不知楼上的有没有明白结果是怎样的？呵呵

[ORARichard]

select * from 表 a,
(
select a_class,a_name from 表
group by a_class,a_name
having count(1)>1
) b
where a.a_class=b.a_class and a.a_name=b.a_name;

[ORARichard]

select a.* from 表 a,
(
select a_class,a_name from 表
group by a_class,a_name
having count(1)>1
) b
where a.a_class=b.a_class and a.a_name=b.a_name;

[ATGC]

诶，原来楼主是要显示所有的符合条件的记录数大于等于2的

[GerryYang]

select * from a where (a_class,a_name) in
( select a_class,a_name from 表
group by a_class,a_name
having count(1)>1
)

[zh430]

就是分组了，然后查每组的记录数，大于1的就是重复的了