SELECT id, string_agg(year_range, ‘, ‘) AS year_ranges FROM ( SELECT id, CASE WHEN count() > 1 THEN min(year)::text || ‘-‘ || max(year)::text ELSE min(year)::text END AS year_range FROM ( SELECT , row_number() OVER (ORDER BY id, year) - year AS grp FROM ( SELECT id, unnest(years) AS year FROM (VALUES (2::int, ‘{1999,2000,2010,2011,2012}’::int[]) ,(3, ‘{1990,1991,2007}’) ) AS tbl(id, years) ) sub1 ) sub2 GROUP BY id, grp ORDER BY id, min(year) ) sub3 GROUP BY id ORDER BY id
产生 恰到好处 的结果。
如果处理数组varchar(varchar[]
,则将其转换为int[]
,然后再继续操作。这似乎是完全合法的形式:
years::int[]
FROM (VALUES (2::int, '{1999,2000,2010,2011,2012}'::int[])
,(3, '{1990,1991,2007}')
) AS tbl(id, years)
->
FROM tbl
由于我们处理的是 (年份),因此我们可以使用快捷方式来形成连续年份(形成范围)的组。我从行号中减去年份本身(按年份排序)。对于连续的年份,行号和年份都加一并产生相同的grp
数字。否则,新范围开始了。