GROUP BY结合HAVING实现查询重复数据

GROUP BY 字段名，以该字段名已经分类，HAVING可以对分完类的数据使用聚合函数

例如

SELECT *  FROM student  WHERE  stu_name IN
(SELECT stu_name  FROM  student GROUP BY stu_name HAVING COUNT(stu_name)>1)

　　

 

 例如

SELECT *  FROM student  WHERE  id IN
(
SELECT id  FROM  student GROUP BY id HAVING MAX(id)>3
)

　　

 

 例如

SELECT  *  FROM  student GROUP BY stu_name

　　

 

 相关sql

CREATE TABLE student(
 `id` INT(11)  PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 stu_name VARCHAR(50) COMMENT '学生名称',
 stu_id VARCHAR(20) COMMENT '学生编号'
)
SELECT  *  FROM  student
INSERT INTO student(stu_name,stu_id) VALUE
('小六','A1000'),
('小六','A1001'),
('小白','A1002'),
('小白','A1003'),
('小红','A1004'),
('小黑','A1005'),
('丸子','A1006'),
('静香','A1007')