One user has more than one role, please query for other users who have all the roles of that user in the change table

DROP TABLE IF EXISTS ' emp ';

CREATE TABLE ' emp ' (
' id ' int (one) not NULL auto_increment,
' Number ' int (one) DEFAULT NULL,
' Name ' varchar (255) DEFAULT NULL,
' Role ' varchar (255) DEFAULT NULL,
PRIMARY KEY (' id ')
) ；

INSERT into ' emp ' VALUES (1,1001, ' a ', ' read ');
INSERT into ' emp ' VALUES (2,1001, ' A ', ' write ');
INSERT into ' emp ' VALUES (3,1001, ' A ', ' copy ');
INSERT into ' emp ' VALUES (4,1002, ' B ', ' read ');
INSERT into ' emp ' VALUES (5,1002, ' B ', ' write ');
INSERT into ' emp ' VALUES (7,1003, ' C ', ' listen ');
INSERT into ' emp ' VALUES (8,1003, ' C ', ' read ');
INSERT into ' emp ' VALUES (9,1004, ' d ', ' read ');
INSERT into ' emp ' VALUES (10,1004, ' d ', ' write ');
INSERT into ' emp ' VALUES (11,1005, ' e ', ' read ');
INSERT into ' emp ' VALUES (12,1005, ' e ', ' write ');

There are two ways to query (Mysql)

1.select Ep.number,ep.role from EMP EP,

(select Role,number from emp where number=1002) E

where e.role= ep.role and e.number!= Ep.number

GROUP BY number

Have count (*) = (select count (number) from EMP where number=1002);

After having a having to write count (*) instead of count (number), the error will be

Results:

Number Count (*)

1001 2
1004 2
1005 2

2.select Number,count (*) from EMP

where role in (select role from EMP where number=1002)

and number!=1002

GROUP BY number

Have count (number) = (select count (number) from EMP where number=1002);

Number Count (*)

1001 2
1004 2
1005 2

One user has more than one role, please query for other users who have all the roles of that user in the change table