日期:2014-05-17  浏览次数:20722 次

oracle 请教各位sql语句,,,,

表table,以下是表里的数据
member_id user_id
0001 11
0001 12
0001 13
0001 14
0002 21
0002 22
0002 23
0002 11
0004 41
0004 42
0004 43
0004 44
0004 22
0005 51

我现在想要查询出每个member_id只显示前两行数据

查询后的结果
member_id user_id
0001 11
0001 12
0002 21
0002 22
0004 41
0004 42
0005 51

请教下各位sql语句,,谢谢!!!!!

------解决方案--------------------
SQL code
with a as
(select '0001' as member_id, '11' as user_id from dual union all
select '0001' as member_id, '12' as user_id from dual union all
select '0001' as member_id, '13' as user_id from dual union all
select '0001' as member_id, '14' as user_id from dual union all
select '0002' as member_id, '21' as user_id from dual union all
select '0002' as member_id, '22' as user_id from dual union all
select '0002' as member_id, '23' as user_id from dual union all
select '0002' as member_id, '11' as user_id from dual union all
select '0004' as member_id, '41' as user_id from dual union all
select '0004' as member_id, '42' as user_id from dual union all
select '0004' as member_id, '43' as user_id from dual union all
select '0004' as member_id, '44' as user_id from dual union all
select '0004' as member_id, '22' as user_id from dual union all
select '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) sub
where 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) sub
where 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

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