使用返回的id在1个查询中跨3个表插入多行

Question

我正在以3个表格的形式记录体育比赛数据：

• 匹配项（id、开始时间）
• 比赛团队（id、比赛id、团队id、分数）
• 比赛球员（id、比赛id、球队id、球员id）

每场比赛由几个队组成，每个队由几个球员组成（实际上是一个名单）。team_id和player_id分别作为球队和球员桌上的外键。

使用上述结构，我需要首先插入matches表，并使用返回的id传递给match_团队和match_玩家。

根据此问题，我使用以下CTE来完成此操作，插入一个匹配项：

WITH a AS (INSERT INTO matches (start_time) 
VALUES ('"0001-01-01T00:00:00+00:00"') 
RETURNING id), 
b AS (INSERT INTO match_teams (match_id, team_id, score) 
VALUES 
((SELECT id FROM a), 5, 1), 
((SELECT id FROM a), 6, 2)) 
INSERT INTO match_players (match_id, team_id, player_id) 
VALUES 
((SELECT id FROM a), 5, 3), 
((SELECT id FROM a), 5, 4),
((SELECT id FROM a), 6, 5)
((SELECT id FROM a), 6, 6); 

我想在一个查询中同时插入多个匹配项。我使用偏移和限制为球员/球队选择正确的比赛id。

WITH a AS (INSERT INTO matches (start_time) 
VALUES 
('"0001-01-01T00:00:00+00:00"'),     -- 1st match
('"0001-01-01T00:00:00+00:00"')      -- 2nd match
RETURNING id), 
b AS (INSERT INTO match_teams (match_id, team_id, score) 
VALUES 
((SELECT id FROM a OFFSET 0 LIMIT 1), 5, 1),     -- 1st match
((SELECT id FROM a OFFSET 0 LIMIT 1), 6, 2),     -- 1st match
((SELECT id FROM a OFFSET 1 LIMIT 1), 5, 2),     -- 2nd match
((SELECT id FROM a OFFSET 1 LIMIT 1), 6, 1))     -- 2nd match
INSERT INTO match_players (match_id, team_id, player_id) 
VALUES 
((SELECT id FROM a OFFSET 0 LIMIT 1), 5, 3),     -- 1st match
((SELECT id FROM a OFFSET 0 LIMIT 1), 6, 4),     -- 1st match
((SELECT id FROM a OFFSET 1 LIMIT 1), 5, 5),     -- 2nd match
((SELECT id FROM a OFFSET 1 LIMIT 1), 6, 6);     -- 2nd match

这是可行的，但这似乎是一个有点老套的html" target="_blank">解决方案。有没有一种最佳实践方法可以做到这一点？

更新我意识到我有一些多余的列。我已经解决了这个问题，但我不认为它会显著改变这个问题。我的问题更像是“使用偏移和限制是否像这样的最佳实践？”

Answer 1

就我而言，我有route\u来源和routes

• route_sources有route_source_id为PK
• 路由有route_source_id为FK

.

-- GOT ID TO ROUTE_SOURCES FROM SEQUENCE
int_route_source_id = nextval('traffic.route_sources_route_source_id_seq'::regclass);

-- CREATE NEW RECORD FOR ROUTE_SOURCES
INSERT INTO traffic.Route_Sources 
    (route_source_id, sql, ini_avl_id, ini_link_id)
VALUES
    (int_route_source_id, strSQL, A.avl_id, A.link_id, ini_offset); 

-- CREATE THE ROUTE
INSERT INTO traffic.Routes 
    (route_source_id, seq_id, node_id, link_id, cost)               
SELECT int_route_source_id, seq, id1 AS node, id2 AS edge, cost::numeric(11,4)
    FROM pgr_trsp; 

Answer 2

使用偏移和限制是否是最佳做法？

当然不是。这是非常低效和非常不方便的使用，尤其是对于大量的匹配。

您可以使用row\u number（）选择合适的id加入团队和玩家加入团队：

with teams (rn, team_ids, scores) as (
    values 
        (1, array[5, 6], array[1, 2]),  -- match #1 in this query
        (2, array[5, 6], array[2, 1])   -- match #2 in this query
    ),
players (rn, team_ids, player_ids) as (
    values 
        (1, array[5, 5, 6, 6], array[3, 4, 5, 6]),
        (2, array[5, 5, 6, 6], array[3, 4, 5, 6])
    ),
ins_matches as (
    insert into matches (start_time) 
    values 
        ('"0001-01-01t00:00:00+00:00"'),
        ('"0001-01-01t00:00:00+00:00"')
    returning id
    ),
matches as (
    select id, row_number() over (order by id) rn
    from ins_matches        -- rn - number of match in this query
    ),
ins_teams as (
    insert into match_teams (match_id, team_id, score) 
    select id, unnest(team_ids), unnest(scores)
    from matches
    join teams using(rn)
    ) 
insert into match_players (match_id, team_id, player_id) 
select id, unnest(team_ids), unnest(player_ids)
from matches
join players using(rn);