我有三个表:POST、POST_ROLE,其中的哪一部分如下POST和ROLE具有多对多关系,它们的联接表是POST_ROLE。
如何获得同时具有不同角色的帖子?
例如,如何获得同时具有角色ROLE_USER和ROLE_ADMIN的帖子?
如下所示的查询不会返回任何内容,因为一行不能为一列有两个值:
SELECT
*
FROM
POST op
JOIN POST_ROLE opr ON
op.POST_ID = opr.POST_ID
JOIN ROLE or1 ON
opr.ROLE_ID = or1.ROLE_ID
WHERE
or1.CODE = 'ROLE_USER' AND or1.CODE = 'ROLE_ADMIN';
另一个使用IN运算符的查询,返回所有三个post行,因为IN操作在此处与OR操作符相同。
SELECT
*
FROM
POST op
JOIN POST_ROLE opr ON
op.POST_ID = opr.POST_ID
JOIN ROLE or1 ON
opr.ROLE_ID = or1.ROLE_ID
WHERE
or1.CODE IN ('ROLE_USER','ROLE_ADMIN');
查询的首选结果应该如下:
POST_ID | TITLE | ROLE_ID | CODE |
---|---|---|---|
1 | 首席执行官 | 100 | 角色用户|
1 | 首席执行官 | 101 | 角色管理员
解决方案是:
SELECT
*
FROM
POST p
WHERE
EXISTS (
SELECT
1
FROM
POST_ROLE pr
JOIN ROLE r ON
r.ROLE_ID = pr.ROLE_ID
WHERE
p.POST_ID = pr.POST_ID
AND r.CODE = 'ROLE_USER')
AND EXISTS (
SELECT
1
FROM
POST_ROLE pr
JOIN ROLE r ON
r.ROLE_ID = pr.ROLE_ID
WHERE
p.POST_ID = pr.POST_ID
AND r.CODE = 'ROLE_ADMIN');