sql – 如何通过不间断的序列来分组条目？

CREATE TABLE entries (
  id serial NOT NULL,title character varying,load_sequence integer
);

和数据

INSERT INTO entries(title,load_sequence) VALUES ('A',1);
INSERT INTO entries(title,2);
INSERT INTO entries(title,3);

INSERT INTO entries(title,6);

INSERT INTO entries(title,load_sequence) VALUES ('B',4);
INSERT INTO entries(title,5);

INSERT INTO entries(title,7);
INSERT INTO entries(title,8);

PostgreSQL中是否有一种方法可以编写SQL,在按load_sequence对它们进行排序后,它们按相同的标题段对数据进行分组.
我的意思是：

=# SELECT id,title,load_sequence FROM entries ORDER BY load_sequence;
 id | title | load_sequence 
----+-------+---------------
  9 | A     |             1
 10 | A     |             2
 11 | A     |             3
 13 | B     |             4
 14 | B     |             5
 12 | A     |             6
 15 | B     |             7
 16 | B     |             8

我想要小组：

=# SELECT title,string_agg(id::text,',' ORDER BY id) FROM entries ???????????;

结果将是：

title | string_agg  
-------+-------------
 A     | 9,10,11
 B     | 13,14
 A     | 12
 B     | 15,16

解决方法

SELECT title,' ORDER BY id)
FROM (
  SELECT id,ROW_NUMBER() OVER (ORDER BY load_sequence) -
         ROW_NUMBER() OVER (PARTITION BY title 
                            ORDER BY load_sequence) AS grp
  FROM entries ) AS t
GROUP BY title,grp

计算的grp字段用于识别具有连续load_sequence值的标题记录的片段.在GROUP BY子句中使用此字段,我们可以在id值上实现所需的聚合.

Demo here

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!