一个用户具有多个角色，请查询出改表中具有该用户的所有角色的其他用户

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