一个简单sql求助

Question

with 
 t1 as
 (
 select '100' as subs_id from dual
 union all
 select '101' as subs_id from dual 
 union all
 select '102' as subs_id from dual
 union all
 select '103' as subs_id from dual
 union all
 select '104' as subs_id from dual
 )
 ,
 t2 as
 (
 select '100' as subs_id,'c01' as status,'1' as sqe from dual
 union all
 select '100' as subs_id,'c02' as status,'2' as sqe from dual
 union all
 select '100' as subs_id,'c02' as status,'3' as sqe from dual
 union all
 select '101' as subs_id,'c02' as status,'1' as sqe from dual
 union all
 select '101' as subs_id,'c01' as status,'1' as sqe from dual
 union all
 select '101' as subs_id,'c02' as status,'2' as sqe from dual
 union all 
 select '102' as subs_id,'c02' as status,'1' as sqe from dual
 union all
 select '103' as subs_id,'c02' as status,'1' as sqe from dual
 union all
 select '104' as subs_id,'c02' as status,'1' as sqe from dual
 ) 
 select t1.subs_id,t2.status,t2.sqe  from  t1 left join t2 on   t1.subs_id = t2.subs_id以上为数据.
sql要求:t1五条记录.
结果为五条记录,右表字段取值要求:
1.当有c01时,只取c01那一条
2.当有c01,c02时,只取c01那一条
3.当只有c02时,按sqe  取最小的那一条.结果为:
 100 c01 1
 101 c01 1
 102 c02 1
 103 c02 1
 104 c02 1
求sql

Answer 1

select a.subs_id,b.status,b.sqe 
  from t1 a,     
       (select * from (select t.*,row_number() over(partition by t.subs_id order by t.status,t.sqe) order_id
            from t2 t) where order_id = 1) b
  where a.subs_id = b.subs_id(+)

Answer 2

把你写的 select t1.subs_id,t2.status,t2.sqe  from  t1 left join t2 on   t1.subs_id = t2.subs_id替换成下面的就行了 select subs_id,status,sqe from (
 select subs_id,
        status,
        sqe,
        case
          when status2 = 'c01' then
           1
          when instr(status2, 'c01') > 0 and status = 'c01' then
           1
          when status2 = 'c02' then 
           row_number() over (partition by subs_id order by sqe asc) 
          when status not in ('c01','c02') then 
           1
          else 
          0
        end as qq from (
         select tt.subs_id,
                tt.status,
                tt.sqe,
                wm_concat(tt.status) over(partition by tt.subs_id) as status2
           from (select t1.subs_id, t2.status, t2.sqe from t1 left join t2 on t1.subs_id = t2.subs_id) tt
           ) 
          )
      where qq > 0

Answer 3

    select t.*,dense_rank() over(partition by sub_id order by BUSI_SEQ desc) as num 
                             from table
我这样解决的，后来又连了一下表,条件是num = 1

Answer 4

那为什么不采用我的做法
dense_rank()和row_number()只是对于完全重复的数据有区别，对于你这题是一样的，甚至可以说row_number()更好，如果有2个c01,1的数据，dense_rank()会显示2个1，这样你就会得到2个c01的数据了row_number()会显示成1和2，你只会得到一条

Answer 5

with 
 t1 as
 (
 select '100' as subs_id from dual
 union all
 select '101' as subs_id from dual 
 union all
 select '102' as subs_id from dual
 union all
 select '103' as subs_id from dual
 union all
 select '104' as subs_id from dual
 )
 ,
 t2 as
 (
 select '100' as subs_id,'c01' as status,'1' as sqe from dual
 union all
 select '100' as subs_id,'c02' as status,'2' as sqe from dual
 union all
 select '100' as subs_id,'c02' as status,'3' as sqe from dual
 union all
 select '101' as subs_id,'c02' as status,'1' as sqe from dual
 union all
 select '101' as subs_id,'c01' as status,'1' as sqe from dual
 union all
 select '101' as subs_id,'c02' as status,'2' as sqe from dual
 union all 
 select '102' as subs_id,'c02' as status,'1' as sqe from dual
 union all
 select '103' as subs_id,'c02' as status,'1' as sqe from dual
 union all
 select '104' as subs_id,'c02' as status,'1' as sqe from dual
 ) 
select subs_id,status,sqe from ( select t1.subs_id,t2.status,t2.sqe,row_number()over(partition by t1.subs_id order by t2.status,t2.sqe) rn from  t1 left join t2 on   t1.subs_id = t2.subs_id)
where rn=1

Answer 6

dense_rank()  是会显示两个1,但我们这边需要，不会出现同一条那样的记录,所以就这样考虑了.
其实用row_number()是完全可以的,  我现在只是想确认一下  分析函数对性能的影响.会不会象distinct 那样，影响特别大.分析函数都进行了哪些操作？会进行全表扫描吗？

Answer 7

 select t1.subs_id, t2.status, t2.sqe
   from t1
   left join (select rank() over(partition by subs_id order by status, decode(status, 'c02', sqe, 1)) as rn,
                     t2.*
                from t2) t2 on t1.subs_id = t2.subs_id
                           and t2.rn = 1