在ORACLE中使用SQL在每个SEG

在ORACLE中使用SQL在每个SEG_ID中取两条记录

 1 with a as( 
 2     select rownum,c.business_id as BUSINESS_ID,c.CUST_ID as CUST_ID,s.id as SEG_ID,k.Queue_Id as Queue_Id,k.Opt_Id as Opt_Id   
 3     from t_customer c
 4     left join t_coll_task k on k.cust_bus_id=c.business_id
 5     left OUTER join T_SEGMENT_INFO s on s.id=c.seg_id
 6     where BUSINESS_ID in(/*同一个CUST_ID取前两条BUSINESS_ID*/
 7         select bid from(
 8             select BUSINESS_ID as bid,row_number() over (partition by CUST_ID order by CUST_ID)seg
 9             from t_Customer) 
10         where seg < 3)/*同一个CUST_ID至少有两条BUSINESS_ID*/
11     and CUST_ID in (select CUST_ID from t_Customer group by CUST_ID having count(*)>=2)
12     order by CUST_ID,BUSINESS_ID)
13   
14 select * from(/*a中重复的BUSINESS_ID取第一条*/
15     select BUSINESS_ID,CUST_ID,SEG_ID,Queue_Id,Opt_Id,ROW_NUMBER() OVER(PARTITION BY BUSINESS_ID ORDER BY BUSINESS_ID)RN FROM a)
16     where RN = 1 
17 order by CUST_ID,BUSINESS_ID

参考：https://www.cnblogs.com/laotan/p/4231099.html

　　　http://blog.csdn.net/yinshan33/article/details/18738229