postgresql 实现取出分组中最大的几条数据

看代码吧~

with name as (
 select
  *
 from
  (
   select
    xzqdm,
    substring (zldwdm, 1, 9) xzdm,
    count (*) sl
   from
    sddltb_qc
   where
    xzqdm in ('130432', '210604')
   group by
    xzqdm,
    substring (zldwdm, 1, 9)
  ) as a
 order by
  xzqdm,
  xzdm,
  sl
) select
 xzqdm,
 xzdm,
 sl
from
 (
  select
   *, row_number () over (
    partition by xzqdm
    order by
     sl desc
   ) as row_id
  from
   name
 ) as a
where
 row_id <= 2
order by
 xzqdm

其中

select * from (select xzqdm,substring(zldwdm,1,9) xzdm,count(*) sl from sddltb_qc where xzqdm in ('130432','210604') group by xzqdm,substring(zldwdm,1,9)) as a order by xzqdm,xzdm,sl

执行结果：

添加行序号：row_number () over (order by a.bsm asc) as 序号

分组添加序号：row_number () over (partition by xzqdm order by a.bsm asc) as 序号

补充：pgsql 表随机取几条数据

取100条

select * from map_route_info_composite order by random() limit 100

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。