sql 多对多该怎么查询呢

theeeeee 发布于 08/14 12:39
阅读 178
收藏 0

用户表:

CREATE TABLE `t_user` (
    `id` INT, 
    `NAME` VARCHAR(40)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

id    用户名

1    user_1
2    user_2
3    user_3
4    user_4
5    user_5

角色表:

CREATE TABLE `t_role` (
    `id` INT, 
    `NAME` VARCHAR(40)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

id    角色名

1    role_1
2    role_2
3    role_3
4    role_4
5    role_5

用户角色中间表:

CREATE TABLE `t_user_role` (
    `user_id` INT, 
    `role_id` INT
) ENGINE = INNODB DEFAULT CHARSET = utf8;

用户id    角色id

1    1
2    1
2    2
3    1
3    2
3    3
4    1
4    2
4    3
4    4
5    1
5    2
5    3
5    4
5    5

INSERT INTO t_user (id,name)VALUES(1,'user_1');
INSERT INTO t_user (id,name)VALUES(2,'user_2');
INSERT INTO t_user (id,name)VALUES(3,'user_3');
INSERT INTO t_user (id,name)VALUES(4,'user_4');
INSERT INTO t_user (id,name)VALUES(5,'user_5');

INSERT INTO t_role (id,name)VALUES(1,'role_1');
INSERT INTO t_role (id,name)VALUES(2,'role_2');
INSERT INTO t_role (id,name)VALUES(3,'role_3');
INSERT INTO t_role (id,name)VALUES(4,'role_4');
INSERT INTO t_role (id,name)VALUES(5,'role_5');

 

INSERT INTO t_user_role (user_id,role_id)VALUES(1,1);
INSERT INTO t_user_role (user_id,role_id)VALUES(2,1);
INSERT INTO t_user_role (user_id,role_id)VALUES(2,2);
INSERT INTO t_user_role (user_id,role_id)VALUES(3,1);
INSERT INTO t_user_role (user_id,role_id)VALUES(3,2);
INSERT INTO t_user_role (user_id,role_id)VALUES(3,3);
INSERT INTO t_user_role (user_id,role_id)VALUES(4,1);
INSERT INTO t_user_role (user_id,role_id)VALUES(4,2);
INSERT INTO t_user_role (user_id,role_id)VALUES(4,3);
INSERT INTO t_user_role (user_id,role_id)VALUES(4,4);
INSERT INTO t_user_role (user_id,role_id)VALUES(5,1);
INSERT INTO t_user_role (user_id,role_id)VALUES(5,2);
INSERT INTO t_user_role (user_id,role_id)VALUES(5,3);
INSERT INTO t_user_role (user_id,role_id)VALUES(5,4);
INSERT INTO t_user_role (user_id,role_id)VALUES(5,5);

怎么查出拥有角色 role_2 或者 role_3 的所有用户呢

加载中
0
前端大师傅
前端大师傅

这个就需要用到分组聚合了,如查出角色拥有的用户,这就很明显的需要以角色作为分组,然后聚合显示每个用户,即一个角色下的所有用户,这时一般数据库都会提供内置函数来完成,如mysql 、sqlite就有GROUP_CONCAT可以以,逗号聚合,而有的数据库如sqlserver则需要通过stuff来自己实现。

当然如果要完整显示三张表的关系,即显示关系表的内容,楼上说的肯定不对。这时需要作行列转换。两种实现方法:

1.写游标动态拼接sql组成字符串然后调用内置函数execute去执行。

2.用程序动态生成sql来执行。

theeeeee
theeeeee
回复 @前端大师傅 : 多谢。
theeeeee
theeeeee
回复 @前端大师傅 : 会行转列
前端大师傅
前端大师傅
回复 @theeeeee : 本身三张表做查询非常复杂,哪有方不方便的。这个也是初级和中级sql开发分水岭。即会不会写行列转换。如果你会行列转换就入门了。如果不会,sql写得再多也是菜鸟。
theeeeee
theeeeee
谢谢。我感觉还是不太方便。
0
S
SilentSnail
查中间表不行吗?
theeeeee
theeeeee
例如:有三个用户【A】【B】【C】 三个角色【X】【Y】【Z】 【A】有角色【X】 【B】有角色【X】【Y】 【C】有角色【X】【Y】【Z】 然后我现在想查询拥有角色【Y】或者【Z】的用户。 我要的结果是查出【B】【C】用户。
0
t
tkhlo

select distinct t.user_id from t_user_role t where t.role_id in (select id from t_role where name in ('role_2','role_3'))

返回顶部
顶部