sql语句：查询包含所有指定角色id的用户

Question

问答

Answer 1

0

云天纵

2017/05/11 10:09

select username from user c inner join (

select uid from user_role a inner join role b on a.rid = b.id where b.id = <input>

) d on c.id = d.uid

大概就是这样，欢迎大神指正，萌新一枚

评论 (4) 引用此答案举报

苍

苍之涛

2017/05/11 10:11

这样不行吧，我的input是多个id

回复举报

Answer 2

0

行者PHPer

2017/05/11 10:23

SELECT username FROM user AS u
INNER JOIN user_role AS ur
INNER JOIN role AS r
WHERE u.id = ur.uid
AND   ur.rid = r.id
AND   ur.rid IN (1,2,3,4);

评论 (4) 引用此答案举报

苍

苍之涛

2017/05/11 10:26

我要的是同时满足，你这个是只要满足一个角色就ok了

回复举报

Answer 3

0

confused_man

2017/05/11 10:38

select uid, group_concat('rid') as rids from user_role group by uid having rids = '1, 2, 3, 4'

先获取uid, 再取详细信息，试试

评论 (2) 引用此答案举报

苍

苍之涛

2017/05/11 10:44

你这个可能用作=好用，用作包含会有问题

回复举报

风翔飞

2017/05/11 10:43

如果需要的rid是1,3你就懵逼了

回复举报

Answer 4

0

风翔飞

2017/05/11 11:01

你的要求是全包含，所以条件就得是a&b&c这种，sql里多是模糊查询，所以sql这样可行

select uid, group_concat('rid') as rids from user_role group by uid having instr(rids,'1')>0 
and instr(rids,'2')>0 and instr(rids,'3')>0 ....

评论 (0) 引用此答案举报

Answer 5

0

云天纵

2017/05/11 11:05

user			role		user_role
id	username	password	id	rolename	uid	rid
1	a	aaa	1	superadmin	1	2
2	b	bbb	2	admin	2	1
3	c	ccc	3	user	3	3
4	d	ddd			4	1

input	<array>	1,2,3
rs
1	superadmin	b,d
2	admin	a
3	user	c

你想要的是最后这个rs的结果吧

评论 (6) 引用此答案举报

Answer 6

0

风翔飞

2017/05/11 11:09

数据太多的话可以在where里进行下筛选

select user_name from user u where exists(

select uid, group_concat('rid') as rids from user_role ur where rid in(1,2,3) and u.u_id=ur.uid group by ur.uid having instr(rids,'1')>0 and instr(rids,'2')>0 and instr(rids,'3')>0 ....)

评论 (4) 引用此答案举报

苍

苍之涛

2017/05/11 11:16

这种方法不如用多个exists in好用，对比字符串很容易出问题

回复举报

苍

苍之涛

2017/05/11 11:14

这是一种方法，我不太想拼sql，还有这里用到了mysql相关函数，想看看还有没有更好的办法。

回复举报

sql语句：查询包含所有指定角色id的用户

以下是问题补充：

苍之涛的其他提问

热门资讯

热门软件