使用UNNEST一个JOIN(Using UNNEST with a JOIN)

我希望能够使用unnest()中，有许多复杂的SQL查询功能在PostgreSQL的JOIN秒。下面是示例查询：

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id
FROM mentions
INNER JOIN taggings ON taggings.mention_id = mentions.id
INNER JOIN tags ON tags.id = taggings.tag_id
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3
GROUP BY tags.parent_id

我想消除taggings这里表，因为我mentions表有一个整数数组字段命名标记列表由所有链接的标签ID的mentions 。

我尝试以下操作：

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id 
FROM mentions 
INNER JOIN tags ON tags.id IN (SELECT unnest(taglist))
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3 
GROUP BY tags.parent_id

这工作，但带来的不仅仅是第一个查询不同的结果。

因此，我想要做的是使用的结果SELECT unnest(taglist)在JOIN查询以补偿taggings表。

我怎样才能做到这一点？

更新： taglist是一组如上面所提到的标签ID的各个列表相同。

Answer 1:

从技术上讲，你的查询可能像这样工作（不能完全肯定这个查询的目标）：

SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT  unnest(m.taglist) AS tag_id
    FROM    mentions m
    WHERE   m.search_id = 3
    AND     9 = ANY (m.taglist)
    ) m 
JOIN   tags t  USING (tag_id) -- assumes tag.tag_id!
GROUP  BY t.parent_id;

然而，在我看来，你是在错误的方向怎么回事。通常情况下一个会删除冗余阵列taglist并保持规范化的数据库架构。那么你的原始查询应该服务好，只是缩短了与别名的语法：

SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM   mentions m
JOIN   taggings mt ON mt.mention_id = m.id
JOIN   tags     t  ON t.id = mt.tag_id
WHERE  9 = ANY (m.taglist)
AND    m.search_id = 3
GROUP  BY t.parent_id;

揭开神秘面纱

<rant>的根本原因为贵“不同的结果”是不幸的命名约定，有些智障的ORM施于人。
我所说的id作为列名。决不多个表使用数据库中的这种反模式。没错，这意味着基本上任何数据库。只要你加入一堆表（也就是你在数据库中做什么）你结束了一堆命名的列的id 。毫无意义。
表命名的ID列tag应当tag_id （除非有其他描述性名称）。决不id 。 </rant>

您的查询无意中计数tags ，而不是mentions ：

SELECT 25 AS keyword_id, count(m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT unnest(m.taglist) AS id
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t USING (id)
GROUP  BY t.parent_id;

它应该是这样的：

SELECT 25 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id
FROM  (
    SELECT m.id, unnest(m.taglist) AS tag_id
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t ON t.id =  m.tag_id
GROUP  BY t.parent_id;

我还添加了回DISTINCT到您的count()是相处中查询的方式丢失。

Answer 2:

像这样的东西应该工作：

...
tags t INNER JOIN
(SELECT UNNEST(taglist) as idd) a ON t.id = a.idd
...

文章来源: Using UNNEST with a JOIN