分组查询方式

下面整理了6种查询方式，根据实际使用场景选择适合的查询方式。

1、with-row_number()

WITH res AS (
SELECT
	id,
	name,
	code,
	range_code,
	ROW_NUMBER() OVER(PARTITION BY range_code ORDER BY code) AS rowid
FROM
	base.d_range_value
)
SELECT * FROM res WHERE rowid=1

2、row_number()

select
	*
from
	(
	select
		id,
		name,
		code,
		range_code,
		row_number() over(partition by range_code order by code) as rowid
	from
		base.d_range_value
) res
where 
rowid = 1

3*、distinct on()

select
	distinct on (range_code)
	id,
	name,
	code,
	range_code
from
	base.d_range_value
order by
	range_code,
	code

4*、with-lateral

with recursive res as(
(
	select
		id,
		name,
		code,
		range_code
	from
		base.d_range_value
	order by
		range_code,
		code
	limit 1
)
union all
select
	u.*
from
	res r,
	lateral(
	select
		id,
		name,
		code,
		range_code
	from
		base.d_range_value
	where
		range_code > r.range_code
	order by
		range_code ,
		code
	limit 1
	) u
)
select * from res

5*、parentTable-lateral

select
	t.*
from
	base.d_range d,
	lateral(
	select
		id,
		name,
		code,
		range_code
	from
		base.d_range_value
	where
		range_code = d.code
	order by
		code
	limit 1
) t

6、array_agg()-group by

select
	(array_agg(id order by code))[1] as id,
	(array_agg(name order by code))[1] as name,
	max(code),
	range_code
from
	base.d_range_value
group by
	range_code

性能比较

分组数据量较小（<100），用3（distinct on()）。
分组数据量较大（>100），分组列有主表时用5，没有用4。

有其他高效的方式，欢迎大家评论补充。
参考自：地址