sql - Postgres : what's wrong with the syntax of this query?

Question

我正在尝试编写一个相当简单的 PSQL 查询来检索一些数据(我意识到这不是目前最有效的查询):

SELECT c.name AS article, c.id AS article_id, t.name AS template, t.id AS template_id, brand_names, COUNT(p.component_id)
FROM publications p
INNER JOIN components c 
(SELECT string_agg(b.name, ', ') AS brand_names 
 FROM brands b
 INNER JOIN brands_components 
 ON b.id = brands_components.brand_id
 WHERE brands_components.component_id = c.id
) brand_query
ON c.id = p.component_id 
INNER JOIN brands_components bc
ON c.id = bc.component_id
AND bc.brand_id IN (16, 23, 24, 35, 37)
INNER JOIN components_templates ct
ON c.id = ct.component_id
INNER JOIN templates t
ON t.id = ct.template_id

尽管在第 4 行，这给了我一个语法错误。缺少什么？如果我单独运行子查询，它工作正常:

syntax error at or near "SELECT" LINE 4: (SELECT string_agg(b.name, ', ') AS brand_names ^ : SELECT c.name AS article, c.id AS article_id, t.name AS template, t.id AS template_id, brand_nam

子查询旨在检索每个组件的所有品牌名称，并将它们显示在一行而不是许多行中。他们的连接表是 brands_components。

可用的 fiddle here ，期望的结果应该是这样的:

article         article_id   template       template_id   count   brands
--------------------------------------------------------------------------------------------------------------
component one | 1          | template one | 1           | 4     | brand one, brand two, brand three, brand four

Answer 1

您的直接问题可以通过横向连接来解决:

SELECT c.name AS article, c.id AS article_id, t.name AS template, t.id AS template_id, brand_names, COUNT(p.component_id)
FROM publications p
  JOIN components c ON c.id = p.component_id 
  JOIN brands_components bc ON c.id = bc.component_id AND bc.brand_id IN (1, 2, 3, 4)
  JOIN LATERAL (
    SELECT b.id, string_agg(b.name, ', ') AS brand_names 
    FROM brands b
      JOIN brands_components ON b.id = brands_components.brand_id
    WHERE brands_components.component_id = c.id
    GROUP BY b.id
  ) brand_query ON brand_query.id = bc.brand_id
  JOIN components_templates ct ON c.id = ct.component_id
  JOIN templates t ON t.id = ct.template_id
GROUP BY 1,2,3,4

上面的代码仍然不会运行，因为 group by 不包括 brand_names 列。 Postgres 不知道 brand_names 已经是聚合。

但是，如果将聚合移动到外部查询，则实际上并不需要派生表:

SELECT c.name AS article, 
       c.id AS article_id, 
       t.name AS template, 
       t.id AS template_id, 
       string_agg(b.name, ',') as brand_names, 
       COUNT(p.component_id)
FROM publications p
  JOIN components c ON c.id = p.component_id 
  JOIN brands_components bc ON c.id = bc.component_id AND bc.brand_id IN (1, 2, 3, 4)
  JOIN brands b on b.id = bc.brand_id
  JOIN components_templates ct ON c.id = ct.component_id
  JOIN templates t ON t.id = ct.template_id
GROUP BY c.name, c.id, t.name, t.id;