sql - 获取多对多关系的聚合 View

Question

我有一个包含 3 列的表，与其他表存在多对多关系。

例子:

主要

| id | brief_id | location_id | company_id | author_id |
|----|----------|-------------|------------|-----------|
| 1  | 22       | 4323        | 9876       | 9762      |
| 2  | 33       | 2675        | 6543       | 1643      |
| 3  | 44       | 2345        | 3445       |           |

中间表:

Location_int
| id | brief_id | location_id |
|----|----------|-------------|
| 1  | 22       | 5413        |
| 2  | 22       | 9833        |
| 3  | 22       | 2364        |

Company_int
| id | brief_id | company_id |
|----|----------|------------|
| 1  | 22       | 6231       |
| 2  | 22       | 6134       |
| 3  | 22       | 2364       |

Author_int
| id | brief_id | author_id |
|----|----------|-----------|
| 1  | 22       | 1345      |
| 2  | 22       | 9213      |
| 3  | 22       | 7255      |

连接表

Location
| id   | location |
|------|----------|
| 5413 | Japan    |
| 9833 | India    |
| 2364 | Africa   |

Company
| id   | Company   |
|------|-----------|
| 5413 | Google    |
| 9833 | Microsoft |
| 2364 | Paypal    |

Author
| id   | Author |
|------|--------|
| 5413 | Tina   |
| 9833 | Saleen |
| 2364 | Sonny  |

我想要的是加入所有这些表并得到:

| brief_id | location             | company                   | Author             |
|----------|----------------------|---------------------------|--------------------|
| 22       | Japan, India, Africa | Google, Microsoft, Africa | Tina, Saleen, Sony |

位置、公司和作者 - 有时所有这些 - 都可以为空，在这种情况下我希望看到空值。

我试过像这样进行左连接:

SELECT
  aur.brief_id,
  string_agg(l.name, ';'),
  string_agg(c.name, ';'),
  string_agg(a.name, ';')

FROM analytics_url_redirect aur
  LEFT JOIN location_int li
    ON bbl.brief_id = aur.brief_id
  LEFT JOIN location l
    ON l.id = li.location_id

  LEFT JOIN company_int ci
    ON bba.brief_id = aur.brief_id
  LEFT JOIN company c
    ON c.id = ci.agency_id

  LEFT JOIN author_int ai
    ON bba2.brief_id = aur.brief_id
  LEFT JOIN author a
    ON a.id = ai.author_id

GROUP BY aur.brief_id;

但这让我多次重复地点、公司和作者。像这样的东西:

| brief_id | location                                                         | company                                              | Author             |
|----------|------------------------------------------------------------------|------------------------------------------------------|--------------------|
| 22       | Japan, India, Africa, Japan, India, Africa, Japan, India, Africa | Google, Microsoft, Africa, Google, Microsoft, Africa | Tina, Saleen, Sony |

完成此任务的更好查询是什么？ (最好在 PostgreSQL 中)还指向一个可以很好优化的查询。仅供引用，所有列都已编入索引。

Answer 1

location 和 company 等表它们之间没有连接条件或 WHERE 过滤器，因此连接它们会产生所有可能的组合行数。

您必须在单独的子查询中为每个摘要计算其他对象:

SELECT brief_id,
       (SELECT group_concat(location)
        FROM Location_int AS li
        JOIN Location AS l ON li.location_id = l.id
        WHERE li.brief_id = aur.brief_id
       ) AS locations,
       (SELECT group_concat(Company)
        FROM Company_int AS ci
        JOIN Company AS c ON ci.company_id = c.id
        WHERE ci.brief_id = aur.brief_id
       ) AS companies,
       (SELECT group_concat(Author)
        FROM Author_int AS ai
        JOIN Author AS a ON ai.author_id = a.id
        WHERE ai.brief_id = aur.brief_id
       ) AS authors
FROM analytics_url_redirect AS aur

(对于 PostgreSQL，将 group_concat 替换为 string_agg。)