oracle sql语句，

with a as(select '0001' as member_id, '11' as user_id from dual union allselect '0001' as member_id, '12' as user_id from dual union allselect '0001' as member_id, '13' as user_id from dual union allselect '0001' as member_id, '14' as user_id from dual union allselect '0002' as member_id, '21' as user_id from dual union allselect '0002' as member_id, '22' as user_id from dual union allselect '0002' as member_id, '23' as user_id from dual union allselect '0002' as member_id, '11' as user_id from dual union allselect '0004' as member_id, '41' as user_id from dual union allselect '0004' as member_id, '42' as user_id from dual union allselect '0004' as member_id, '43' as user_id from dual union allselect '0004' as member_id, '44' as user_id from dual union allselect '0004' as member_id, '22' as user_id from dual union allselect '0005' as member_id, '51' as user_id from dual)select sub.member_id, sub.user_id from(select member_id, user_id, row_number() over (partition by member_id order by member_id, user_id) cnt from a) subwhere sub.cnt < 3;
------解决方案--------------------
哎呀，一样的啊。。。
你改成你自己的表名就完了呗：
SQL code
select sub.member_id, sub.user_id from(select member_id, user_id, row_number() over (partition by member_id order by member_id, user_id) cnt from table) subwhere sub.cnt < 3;
------解决方案--------------------
select memberid,userid from (select memberid,userid,dense_rank() over(partition by memberid order by userid) denserank from danger) where denserank<3

  不需要那么复杂，前几天刚研究oracle函数，只需用分析函数就OK了，我博文有详细介绍。
------解决方案--------------------
也就是你需要显示拥有两个以上member_id的user_id是吧。

select da.memberid,da.userid from (select memberid, userid
 from (select memberid,
              userid,
              dense_rank() over(partition by userid order by memberid) denserank
         from danger d) where denserank<3) da,
      (select userid, max(denserank) m
         from (select memberid,
                      userid,
                      dense_rank() over(partition by userid order by memberid) denserank
                 from danger) group by userid) ma
  where da.userid = ma.userid
  and ma.m > 1

语句复杂了点。。
   应该有个更好的方法。。