Postgres通过查询分组 [英] Postgres group by query
问题描述
我试图在postgres的查询中使用group by。我无法按照我的需要将它工作,以便根据需要对结果进行分组。
I am trying to use group by in a query in postgres. I cannot get it to work as I would like in order to group results as I need.
这是对 recursion queries我刚刚回答了。但是现在我需要能够将结果分组到最终查询的root_id列。这是以前的查询:
This is an extension to another stack question on recursion queries I just had answered. But now I need to be able to group the results on the root_id column of the final query. Here is the query before:
select cl.parent_comment_id,
cl.article_comment_id,
cl.comment,
cl.article_id,
cl.comment_depth
from comment_list cl
order by cl.root_id, cl.article_comment_id, cl.comment_depth;
以下是我想要做的事情,以便将具有相同parent_comment_id的任何记录保存在一起。 / p>
Here is what I would like to do so that any records with the same parent_comment_id are kept together.
select cl.parent_comment_id,
cl.article_comment_id,
cl.comment,
cl.article_id,
cl.comment_depth
from comment_list cl
group by cl.parent_comment_id
order by cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;
可以有多个记录返回相同的parent_comment_id,但是对于任何给定的article_comment_id都有不同的记录。即每个评论都是唯一的(标识,评论,标题等),但每个父评论可以有许多孩子。这已经被递归查询检索出来了,现在我只是试图将它们正确分组。
There can be many records with the same parent_comment_id returned, but distinct records for any given article_comment_id. i.e. each comment is unique (id, comment, title, etc.) but each parent comment can have many children. This has already been retrieved by the recursive query, now I am just trying to group them correctly.
编辑:
查看 http:/ /www.sqlfiddle.com/#!12/77771/2 。我希望article_comment_id = 6紧跟在article_comment_id = 3之后,因为id = 3是父级。然后article_comment_id = 4。
Take a look at http://www.sqlfiddle.com/#!12/77771/2 .What I would like is for article_comment_id=6 to follow immediately below article_comment_id=3 since id=3 is the parent. Then article_comment_id=4.
但我认为这需要程序化地完成。
However I am thinking this needs to be done procedurally.
所以我认为这是一个'没关系'的问题类型的问题,除非有人知道如何(这就是为什么我要放弃它)。但我认为我会尝试以程序方式解决这个问题。
So I think this is a 'never mind' type of question unless someone knows how (which is why I am leaving it up). But I think I am going to try to solve this part procedurally.
推荐答案
对于递归查询,您可以创建分层路径使用0填充字符串的技巧: SQL小提琴
For your recursive query, you can create a hierarchical path using this trick with 0-padded strings: SQL Fiddle
with recursive comment_list(article_comment_id, parent_comment_id, comment, article_id, comment_depth, comment_path) AS (
select c.article_comment_id,
c.parent_comment_id,
c.comment,
c.article_id,
c.comment_depth,
substr(CAST(1000000000+c.article_comment_id as varchar(1000)),2)
from test_comment c
where article_id = 100
and parent_comment_id = 0
union all
select c.article_comment_id,
c.parent_comment_id,
c.comment,
c.article_id,
c.comment_depth,
cl.comment_path || substr(CAST(1000000000+c.article_comment_id as varchar(1000)),2)
from test_comment c
join comment_list cl on c.parent_comment_id = cl.article_comment_id
)
select cl.article_comment_id,
cl.comment_path,
cl.parent_comment_id,
cl.comment,
cl.article_id,
cl.comment_depth
from comment_list cl
order by cl.comment_path, cl.article_comment_id, cl.comment_depth;
删除GROUP BY。你想分组他们的显示,这是真正的ORDER BY
Drop the GROUP BY. You want to "group" them for display, which is really "ORDER BY"
select cl.parent_comment_id,
cl.article_comment_id,
cl.comment,
cl.article_id,
cl.comment_depth
from comment_list cl
order by cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;
您可能需要也可能不需要 cl.root_id
按顺序排列,因此它可以是
You may or may not still need the cl.root_id
in the order by, so it could be
order by cl.root_id, cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;
这篇关于Postgres通过查询分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!