postgresql 将 UNNEST 与 JOIN 一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16054925/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using UNNEST with a JOIN
提问by ?lker ?nan?
I want to be able to use unnest()function in PostgreSQL in a complicated SQL query that has many JOINs. Here's the example query:
我希望能够unnest()在具有许多JOINs的复杂 SQL 查询中使用PostgreSQL 中的函数。这是示例查询：
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
I want to eliminate the taggingstable here, because my mentionstable has an integer arrayfield named taglistthat consists of all linked tag ids of mentions.
我想消除taggings这里的表，因为我的mentions表有一个名为taglist的整数数组字段，它由.mentions
I tried following:
我尝试了以下操作：
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
This works but brings different results than the first query.
这有效，但带来了与第一个查询不同的结果。
So what I want to do is to use the result of the SELECT unnest(taglist)in a JOINquery to compensate for the taggingstable.
因此，我想要做的是使用的结果SELECT unnest(taglist)的JOIN查询，以补偿taggings表。
How can I do that?
我怎样才能做到这一点？
UPDATE:taglistis the same set as the respective list of tag ids of mention.
更新：taglist与提及的标签 ID 的相应列表相同。
采纳答案by Erwin Brandstetter
Technically, your query might work like this (not entirely sure about the objective of this query):
从技术上讲，您的查询可能像这样工作（不完全确定此查询的目标）：
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;
However, it seems to me you are going in the wrong direction here. Normally one would remove the redundant array taglistand keep the normalized database schema. Then your original query should serve well, only shortened the syntax with aliases:
但是，在我看来，您在这里走错了方向。通常，人们会删除冗余数组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;
Unravel the mystery
揭开谜底
<rant>The root cause for your "different results" is the unfortunate naming convention that some intellectually challenged ORMsimpose on people.
I am speaking of idid. Utterly pointless.
The ID column of a table named tagshould be tag_id(unless there is another descriptive name). Never id.
</rant>
<rant>您的“不同结果”的根本原因是一些在智力上受到挑战的 ORM强加给人们的不幸的命名约定。
我说的idid. 完全没有意义。
命名的表的 ID 列tag应该是tag_id（除非有另一个描述性名称）。从来没有id。
</rant>
Your query inadvertently counts tagsinstead of mentions:
您的查询无意中计数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;
It should work this way:
它应该这样工作：
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;I also added back the DISTINCTto your count()that got lost along the way in your query.
我还添加回DISTINCT您count()在查询过程中丢失的 。
回答by Wojtas
Something like this should work:
这样的事情应该工作：
...
tags t INNER JOIN
(SELECT UNNEST(taglist) as idd) a ON t.id = a.idd
...
