mysql查询用户张三有哪些角色_一个用户具有多个角色，请查询出改表中具有该用户的所有角色的其他用户...

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`number` int(11) 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');

有两种方式可以查询(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

having count(*)=(select count(number) from emp where number=1002 )；

在这里having 后的 要写count(*) 而不是count(number) 否则会报错

结果:

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

having count(number) =(select count(number) from emp where number=1002);

number count(*)

1001    　　   2

1004　　　　2

1005　　　　2