oracle分组排序

oracle 分组排序：

这个麻烦：
SELECT * FROM (
     SELECT deptno, ename, sal, ROW_NUMBER()    OVER (PARTITION BY deptno ORDER BY sal DESC     ) Top3 FROM emp
)
WHERE Top3 <= 3


开窗函数也ok：代码简单点：
 where 1=1 and status=2
JOIN( select pid,min(id) as id from T_PRODUCT group by pid) firstp ON firstp.id=p.id


mybatis应用：
 where 1=1 and status=2
JOIN( select pid,min(id) as id from T_PRODUCT  WHERE STATUS=2 and LIFESTATUS &gt; 0 group by pid) firstp ON firstp.id=p.id

分组最大最小：

min()/max() over(partition by  pid order by id)

分组分页：

-- 使用ROW_NUMBER分页，查找第1-10条数据


SELECT T.custid,T.companyname,T.address,T.city FROM 
(
    SELECT ROW_NUMBER() OVER(ORDER BY custid) AS rownum,custid,companyname,address,city FROM Sales.Customers
) AS TWHERE T.rownum BETWEEN 1 AND 10


促销选择商品应用例子：
SELECT pp.*,PA.*
    FROM (select t.* from (
  select p.id,p.productcode,p.introduce,p.delivery_address,p.reason,p.catalogID,p.name,p.picture,p.price,p.nowPrice,
     p.isnew,p.sale,p.hit,p.title,p.createAccount,to_char(p.createtime,'yyyy-MM-dd hh24:mi:ss') createtime,
     to_char(p.updatetime,'yyyy-MM-dd hh24:mi:ss') updatetime,
     b.between_price AS areaprice,
     p.status,p.sellcount,b.stock,p.giftID,p.images,
     (SELECT name FROM T_CATALOG cat WHERE cat.ID=p.CATALOGID) as catalogname,
       (SELECT unit FROM T_CATALOG cat WHERE cat.ID=p.CATALOGID) as unit,
     p.pid,p.packlisting,p.productplace,
     b.COUNTPID countpid,DENSE_RANK() OVER(ORDER BY p.pid) AS mrownum
  from t_product p
 
     join (select * from between_price_view  where area_id ='331946' OR (area_id =0)  ) b on b.pid=p.pid   
       where 1=1    
     order by p.updatetime desc,p.id 
    ) t   where mrownum  <= 0+20
  ) pp   
    
LEFT JOIN (
SELECT * FROM (select productid,area_id,price from T_PRICE_AREA)  
   PIVOT (
          sum(price)      
          FOR area_id
          IN  (331518,331946,333168)  
        )) pa  ON pa.PRODUCTID = pp.id
     where pp.mrownum >10

文章来源: blog.csdn.net，作者：网奇，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/jacke121/article/details/54474155