postgres窗口函数分页与排序问题

最新推荐文章于 2024-10-17 19:11:48 发布

sober.guo

最新推荐文章于 2024-10-17 19:11:48 发布

阅读量131

点赞数 2

文章标签： java 数据库 sql

本文链接：https://blog.csdn.net/qq_29410345/article/details/140829043

版权

postgres由于使用了窗口函数，分页查询必须手动指定排序，否则拿到的数据永远是第一页

select id,vendor_id,vendor_cont_id,vendor_cont_code,goods_id,buyer_id,buyer_name,pur_dept_id,pur_dept_name,tenant_id,tenant_code,system_code,company_id 
  from 
  (
  select
      row_number() OVER (ORDER BY (SELECT NULL)) AS id,
  a.vendor_id,
  a.vendor_cont_id,
  cont.vendor_cont_code,
  b.goods_id,
  a.buyer_id,
	dce.emp_code buyer_code,
  dce.emp_name buyer_name,
  a.pur_dept_id,
	dhcoi.dept_code as pur_dept_code,
  dhcoi.dept_name as pur_dept_name,
  1224 as tenant_id,
  'guoyao-lerentang' as tenant_code,
  '1224' as system_code,
    a.owner_id as company_id,
    row_number() over (partition by
      a.owner_id,
      b.goods_id,
      a.vendor_id,
          a.system_code,
          a.vendor_cont_id 
          order by b.modify_date,b.create_date desc
          ) rn
  from
    dwi_pur_ps_channel_hdr a
  inner join dwi_pur_ps_channel_dtl b 
  on
    a.pur_chl_hdr_id = b.pur_chl_hdr_id
    and a.system_code = b.system_code
  left join sjzt_rebate_admin.dim_cms_emp dce 
  on
    a.buyer_id = dce.emp_id
    and a.system_code = dce.system_code
  left join sjzt_rebate_admin.dim_qms_goods_basic dqgb on
    a.system_code = dqgb.system_code
    and b.goods_id = dqgb.scs_goods_files_id
  left join sjzt_rebate_admin.dwi_hrs_cms_org_info dhcoi on
    a.pur_dept_id = dhcoi.dept_id
    and a.system_code = dhcoi.system_code
  left join dwi_qms_cvm_vend_cont_info cont on
    cont.vendor_cont_id = a.vendor_cont_id
    and cont.system_code = a.system_code
  where
    a.system_code = '1224'
    and a.owner_id = '18'
      and dce.stop_flag = 00
    and dce.logical_is_deleted = 0

  	and a.vendor_id = 3681
  	and a.vendor_cont_id = 60236
  	and b.goods_id = 10166
  ) tmp where tmp.rn = 1 limit 2000 offset 0