PG 实现分页功能

三个关键词：Index,Row_number()和 limit

 

       select *  
          from  
               (select   
                       row_number() over( order by huiyuan.vip desc,huiyuan.time  ) as orderid,
                       count(1) over() as n_count,
                       huiyuan.id,
     com.com_name 
                  from   com join huiyuan
                  on      com.cust_id = huiyuan.id
                  where  huiyuan.verify >=  3   
                              and   huiyuan.cust_kind = '物流公司'  
            and com.city='郑州市'
                  limit  10                   
                )t
          where  orderid > 0 
         order  by orderid 

 

sqlserver分页：

关键字：

 ROW_NUMBER ( ) OVER ()

 COUNT ( 1 ) OVER ( )

SELECT
    * 
FROM
    (
    SELECT
    ROW_NUMBER ( ) OVER ( ORDER BY CASE px WHEN 1 THEN 1 ELSE 2 END, zs.shenqingSJ DESC ) AS Row,
    zs.id,
    zs.biaoti,
    zs.add_com_name,
    zs.dianhua,
    zs.add_url,
    zs.add_domain,
    zs.qq,
    zs.xinxi,
    zs.shenqingSJ,
    zs.province,
    zs.city,
    zs.county,
    zs.cust_kind,
    zs.cust_id,
    zs.add_vip,
    zs.add_cust_name ,
    zs.add_pic ,
    COUNT ( 1 ) OVER ( ) AS CountRow 
FROM
    zhaoshang zs 
WHERE
    zs.shenhe= 1 
    AND zs.add_Verify> 2 
    ) AS T 
WHERE
    Row BETWEEN 1 
    AND 20