SQL交叉引用表自引用

Question

我正在做一个项目，我有一张

all_names(
  team_name TEXT,
  member_name TEXT,
  member_start INT,
  member_end INT);

我的任务是创建一个表

participants(
  ID SERIAL PRIMARY KEY,
  type TEXT,
  name TEXT);

其中包含所有团队和成员名称作为自己的条目。类型可以是“团队”或“成员”。

为了赞美这张参与者表，我正在尝试创建一个交叉引用表，允许通过 ID 将成员引用到团队，反之亦然。我的表看起来像这样:

belongs_to(
  member_id INT REFERENCES participants(ID),
  group_id INT REFERENCES  participants(ID),
  begin_year INT,
  end_year INT,
  PRIMARY KEY (member_id, group_id);

我不确定如何继续并正确填充表格。

到目前为止我的选择查询是:

SELECT DISTINCT ON (member_name, team_name)
  id, member_name, team_name, member_begin_year, member_end_year
FROM all_names
INNER JOIN artists ON all_names.member_name = participants.name;

但我不确定如何进行。填充交叉引用表的正确方法是什么？

Answer 1

可能最简单的解决方案是使用一些语句。包装这是一个事务以确保您不会遇到并发问题:

BEGIN;

INSERT INTO participants (type, name)
  SELECT DISTINCT 'team', team_name
  FROM all_names
  UNION
  SELECT DISTINCT 'member', member_name
  FROM all_names;

INSERT INTO belongs_to 
  SELECT m.id, g.id, a.member_start, a.member_end
  FROM all_names a
  JOIN participants m ON m.name = a.member_name
  JOIN participants g ON g.name = a.team_name;

COMMIT;

属于多个团队的成员会记录他们的所有成员资格。