查询表中的重复记录

创建表

CREATE TABLE `teacher` (                
           `id` int(11) NOT NULL auto_increment, 
           `name` varchar(20) default NULL,      
           `age` int(11) default NULL,           
           PRIMARY KEY  (`id`)                   
         )

-- 查找不重复的列
-- select distinct(name) from nihao.teacher;

-- 查找不重复的记录(所有列都不重复)
-- select distinct * from teacher

-- select distinct * from teacher;

-- 查找某个列(name)不重复的记录
-- select * from teacher where id in (select max(id) from teacher group by name)

-- 查找某及几个列(name，age)不重复的记录
-- select * from teacher where id in (select max(id) from teacher group by name, age)

-- 查找某个列(name)出现重复的记录(显示全部重复记录)
-- select * from teacher where name in(select name from teacher  group by name having(count(*)>1 ))

-- 查找多个列（name,age）出现重复的记录(只显示一行记录)
-- select * from teacher  group by name,age having(count(*)>1)

-- 查找某个列(name)出现重复的记录(只显示一行记录)
select * from teacher where name in(select name from teacher group by name having(count(*)>1)) and age in(select age from teacher group by age having(count(*)>1))