如何使用来自两个表的数据建立关系？

问题描述：

我在Postgres中构建一个迁移，我需要为每个组织添加一个新用户。如何使用来自两个表的数据建立关系？

假设我有树表：

用户
组织
user_org

，并在数据库已经存在的机构名单。

现在我根据其他用户和修改后的数据，像这样创建新用户：

insert into users 
select 
    ... 
from users where ... 
returning id

我现在需要的是每个用户（关联到一个组织的假设数创建的用户和机构单位的数匹配）。

cross join不会工作，因为它会将所有用户关联到所有组织。

我从已经与orgs关联的用户列表中，有一种简单的方法可以基于此建立我的查询，因为新创建的用户具有不同的密钥？

不知道我完全理解你的问题。假设你有10个组织，你想插入10个用户，每个组织有1个用户？也许样本数据和预期结果会有所帮助。 – sgeddes

@sgeddes正确 –

Postgres版本和表定义将会膨胀。 –

答

如果我理解正确的话，这里是使用inner join一个选项，然后在建立row_number每个表join：

insert into user_org 
select u.id, o.id 
from (select id, row_number() over (order by id) rn 
     from usr) u join 
    (select id, row_number() over (order by id) rn 
     from org) o on u.rn = o.rn

SQL Fiddle Demo

我会尝试 –

看起来它是做我所需要的，我会做更多的测试并回传。谢谢！ –

两个子查询中的'order by id'都要求'usr.id'和'org.id'的* values *是同步的 - 这是一个不太可能的假设。当然不是基于问题中的信息。通常，此查询不起作用。 –

答

名单我从已经与orgs相关联的用户复制，有没有一种简单的方法来建立我的查询基于此，鉴于新创建的用户有不同的钥匙？

如果有任何列的子集来标识没有PK的唯一用户，那么存在一个安全的数据修改CTE解决方案。你甚至可以同时插入多个用户副本：

在，你不应该有一个可靠的组列加入到模板的用户较少的情况下：添加行号，像@sgeddes already suggested，但在实际工作的查询：

WITH template_usr AS (
    SELECT u.*, uo.org_id -- include the connected org_id 
      row_number() OVER() AS rn -- arbitrary number 
    FROM usr  u 
    JOIN usr_org uo ON uo.usr_id = u.id -- guessing column names 
    WHERE ... -- make sure exactly *one* desired org is joined 
    ) 
, ins_usr AS (
    INSERT INTO usr u (...) -- add target columns 
    SELECT ... 
    FROM template_usr 
    ORDER BY rn    -- insert in same order! 
    RETURNING id 
    ) 
INSERT INTO usr_org (usr_id, org_id) 
SELECT iu.id, tu.org_id 
FROM (
    SELECT id, row_number() OVER (ORDER BY id) AS rn 
    FROM ins_usr 
    ) iu 
JOIN template_usr tu USING (rn) 
RETURNING *; -- optional

如何使用来自两个表的数据建立关系？

相关推荐