Oracle查询分组后获取首尾两条记录

      SELECT max(end_oil_cost) - min(start_oil_cost) into Result
      FROM (SELECT ROW_NUMBER() OVER(PARTITION BY d.terminal_id ORDER BY d.clct_date DESC) LEV,
                   count(0) over(partition by d.terminal_id) cnt,
                   d.*
              FROM t_b_car_info car, t_b_car_terminal ct, t_o_day_info d
             where car.id = ct.car_id
               and ct.terminal_id = d.terminal_id
               and d.clct_date >= nStartDateEx
               and d.clct_date <= nEndDateEx
               and d.clct_date between ct.terminal_install_date and
                   ct.terminal_uninstall_date
               and car.id = nCarID
               and d.end_oil_cost > 0
               and d.start_oil_cost > 0)
     WHERE LEV = 1
        or LEV = cnt

     ORDER BY clct_date DESC

————————————————————————————————————————————————————

对多数据进行分组排序后取每组第一条记录

 

Sql代码 

SELECT *     

  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY <PK_FINANCE_ACCOUNT_1> ORDER BY <amassdate> DESC) LEV,     

               <hx_amass_liquid>.*     

          FROM <hx_amass_liquid>)     

 WHERE LEV = 1     

 ORDER BY <PK_FINANCE_ACCOUNT_2> DESC   

 

SELECT *  

  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY <PK_FINANCE_ACCOUNT_1> ORDER BY <amassdate> DESC) LEV,  

               <hx_amass_liquid>.*  

          FROM <hx_amass_liquid>)  

 WHERE LEV = 1  

 ORDER BY <PK_FINANCE_ACCOUNT_2> DESC 

 

<PK_FINANCE_ACCOUNT_1> -- 分组列

 

 

 

<amassdate> -- 排序列

 

 

 

<hx_amass_liquid> -- 表名

 

 

 

<PK_FINANCE_ACCOUNT_2> -- 排序列

 

 

 

注：<hx_amass_liquid>.* -- 一定要有表名，否则会报“缺失表达式”错误

 

 

 

==========================================================

 

 

 

分组时不仅想取每组的第一条记录，还想取每组共有几条记录

 

 

Sql代码 

SELECT *  

FROM (  

 

 

SELECT ROW_NUMBER() OVER(PARTITION BY PK_FINANCE_ACCOUNT ORDER BY amassdate DESC) LEV,  

               

 

             count(0) over(partition by PK_FINANCE_ACCOUNT) cnt,  

 

               

 

             hx_amass_liquid.*  

 FROM   

 

 

 hx_amass_liquid  

 

 

)  

 

 

 WHERE LEV = 1  

 ORDER BY PK_FINANCE_ACCOUNT DESC