您可以使用间隙和孤岛方法，然后进行聚合。以下内容获取组：

select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end)
from (select tt8.*, row_number() over (order by c1) as seqnum
      from tt8
     ) t
group by (c1 - seqnum);

然后可以将它们放入一个字符串中：

select string_agg(val, ',' order by min_c1)
from (select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end) as val, min(c1) as min_c1
      from (select tt8.*, row_number() over (order by c1) as seqnum
            from tt8
           ) t
      group by (c1 - seqnum)
     ) t;

这是一把小提琴。

请尝试一下：

with trans as (
  select c1, 
         case when lag(c1) over (order by c1) = c1 - 1 then 0 else 1 end as new
    from tt8
), groups as (
  select c1, sum(new) over (order by c1) as grpnum
    from trans
), ranges as (
  select grpnum, min(c1) as low, max(c1) as high
    from groups
   group by grpnum
), texts as (
  select grpnum, 
         case 
           when low = high then low::text 
           else low::text||'-'||high::text
         end as txt
    from ranges
)
select string_agg(txt, ',' order by grpnum) as answer
  from texts;

    answer     
---------------
 1-3,6-9,12,14
(1 row)

您可以更改最后一个查询以返回每个cte的结果，以查看发生了什么。 trans 使用 lag() 用于标记以组开头的行的窗口函数：

c1 | new 
----+-----
  1 |   1
  2 |   0
  3 |   0
  6 |   1
  7 |   0
  8 |   0
  9 |   0
 12 |   1
 14 |   1
(9 rows)
``` `groups` 使用 `sum()` 隐式窗口函数 `unbounded preceding` 为每行指定一个 `grpnum` :

c1 | grpnum
----+--------
1 | 1
2 | 1
3 | 1
6 | 2
7 | 2
8 | 2
9 | 2
12 | 3
14 | 4
(9 rows)
``` ranges 折叠每个 groupnum 到它的 min() 以及 max() :

grpnum | low | high 
--------+-----+------
      3 |  12 |   12
      4 |  14 |   14
      2 |   6 |    9
      1 |   1 |    3
(4 rows)
``` `texts` 翻译 `low` 以及 `high` 文本表示的范围：

grpnum | txt
--------+-----
3 | 12
4 | 14
2 | 6-9
1 | 1-3
(4 rows)

这个 `string_agg()` 打开 `txt` 将值放入逗号分隔的列表中。