从子查询中选择/打印数据
从子查询中选择/打印数据
提问于 2019-08-23 14:56:32
在我的应用程序中，我有一个表"user"，一个表“角色”，和一个表“用户角色”。用户角色是由user, role组成的简单表，其中user对应于user表的id列。因为用户可以有多个角色，很明显，多个用户可以拥有相同的角色。现在，我希望找到所有没有特定角色(private_account)的用户，我希望将他们作为(user_id, new-inserted-role-id)进行进一步处理。
SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
SELECT ur.user
FROM public."userroles" as ur
INNER JOIN public."role" as r
ON ur.role=r.id
WHERE r."roleDescription"='private_account'
)但是，这会导致表r不存在。
ERROR: missing FROM-clause entry for table "ur"
我该怎么做？
因此，为了清楚起见:如果private_account有id 2和我是没有这个ID的用户1，则返回如下所示：
USER | role
1 | 2
2 | 2报告的角色独立于用户实际拥有的内容。我可以手动完成这一操作，并进行如下选择。但是，这有一个巨大的缺点，如果我移动到另一个数据库，就必须手动更新sql (因为ID可能不符合实际的角色名称)。
SELECT u.id, 2
...进一步的处理基本上是使用select作为INSERT INTO返回：
INSERT INTO public."userroles"
SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
SELECT ur.user
FROM public."userroles" as ur
INNER JOIN public."role" as r
ON ur.role=r.id
WHERE r."roleDescription"='private_account'
)回答 2
Database Administration用户
发布于 2019-08-23 17:21:08
创建表格：
postgres=# create table "user" (
id int primary key,
name text unique);
postgres=# create table "role" (
id int primary key,
name text unique);
postgres=# create table "userroles" (
"user" int references "user",
"role" int references "role",
primary key("user", "role")
);填充测试数据
postgres=# insert into "user" values (1, 'privateuser'), (2, 'publicuser');
postgres=# insert into "role" values (1, 'private_account'), (2, 'other_role');
postgres=# insert into userroles values (1,1), (1,2), (2,2);查找所有用户及其角色：
postgres=# SELECT u.id, u.name as "user", r.id, r.name as "role"
FROM "user" u
LEFT JOIN "userroles" ur ON ur.user=u.id
LEFT JOIN "role" r ON r.id=ur.role;
id | user | id | role
----+-------------+----+-----------------
1 | privateuser | 1 | private_account
1 | privateuser | 2 | other_role
2 | publicuser | 2 | other_role
(3 rows)查找所有没有特定角色的用户：
postgres=# select u.id, u.name as "user", r.id, r.name as "role"
FROM "user" u
LEFT JOIN userroles ur ON ur.user=u.id
LEFT JOIN "role" r ON r.id=ur.role
WHERE u.id NOT IN (
SELECT "user" FROM userroles ur2, "role" r2
WHERE r2.id=ur2.role AND r2.name='private_account');
id | user | id | role
----+------------+----+-------------
2 | publicuser | 2 | other_role
(1 row)最后一个问题是你的答案。
Database Administration用户
发布于 2019-08-24 13:07:54
您只需执行另一个子查询：
SELECT
u.id,
(select r2.id from public."role" as r2 where r2."roleDescription"='private_account') as missing_role_id
FROM public."user" AS u
WHERE u.id NOT IN (
SELECT ur.user
FROM public."userroles" as ur
INNER JOIN public."role" as r
ON ur.role=r.id
WHERE r."roleDescription"='private_account'
)如果您反对“private_account”在查询中出现两次，则将其放在CTE后面。
WITH j as (select id from public."role" where "roleDescription"='private_account')
SELECT
u.id,
(select id from j) as missing_role_id
FROM public."user" AS u
WHERE u.id NOT IN (
SELECT ur.user
FROM public."userroles" as ur
WHERE ur.role=(select id from j)
)页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/246115
复制相关文章
相似问题
腾讯云开发者
