使用over+ PARTITION BY实现分组
select d."form_id",
d.id,
d.created_at,
count(*) OVER(
PARTITION BY d."form_id" )
from document d
where d.status='1'
效果
使用 ROW_NUMBER() +over函数配合实现组内排序
select d."form_id",
d.id,
d.created_at,
count(*) OVER(
PARTITION BY d."form_id" ),
ROW_NUMBER() OVER(
PARTITION BY d."form_id"
ORDER by d.created_at ) as row1
from document d
where d.status='1'
借用临时表判断row1=1的记录,去掉重复的分组id
select *
from ( select d."form_id",
d.id,
d.created_at,
count(*) OVER(
PARTITION BY d."form_id" ),
ROW_NUMBER() OVER(
PARTITION BY d."form_id"
ORDER by d.created_at ) as row1
from document d
where d.status='1' ) temp
where temp.row1=1
达到最终效果: