【查询表内名字相同的人】

查询表内名字相同的人

drop table if exists emp;
create table emp (
	id int primary key auto_increment,
    name varchar(20),
    sex varchar(1),
    depart varchar(20),
    salary int
);

insert into emp (name,sex,depart,salary) values 
("张三","男","人事",12000),
("李四","女","财务",8000),
("赵五","女","人事",9000),
("李一","女","物流",22000),
("王六","女","人事",4000),
("董七","男","企划",2000),
("陈八","男","财务",10000),
("耿九","男","财务",1000),
("孙二","女","人事",19000),
("李十","男","物流",7000),
("张三三","男","企划",8000),
("李四四","男","企划",9000),
("李十","女","人事",22000),
("李十","女","财务",8000),
("张三三","女","人事",9000),
("董七","男","物流",22000),
("陈八","女","人事",4000)

SELECT * FROM emp 
WHERE name IN
(SELECT name FROM emp 
GROUP BY name 
having COUNT(name)>1)
ORDER BY name

SELECT p1.* 
FROM emp p1 
INNER JOIN 
emp p2 
on p1.name=p2.name 
and p1.id<>p2.id 
GROUP BY id 
ORDER BY name