表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语句,,谢谢!!!!!
(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;
这样改怎么办。
你改成你自己的表名就完了呗:
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;
查询结果为member_id user_id
0001 11
0002 11
0002 22
0004 22
7楼已经回答了,用dense_rank() 函数,直接搞定!
我已经试过了,可以查出来,但是我只想显示user_id相同的数据,,,8楼的回复还会把user_id不同的也显示出来请教8楼怎样只显示user_id相同的数据
member_id user_id
0001 11
0002 11
0002 22
0004 22
select memberid,userid from (select memberid,userid,dense_rank() over(partition by userid order by memberid) denserank from danger) where denserank<3这个字段调换后的sql查询出来的结果不是你想要的吗?
但是查出的结果是
member_id user_id
0001 11
0002 11
0002 22
0004 22
0001 12
0001 13以下是我想要的结果member_id user_id
0001 11
0002 11
0002 22
0004 22
我的要的结果是
member_id user_id
0001 11
0002 11
0002 22
0004 22不要显示以下数据的后6条,只要前4条数据
member_id user_id
0001 11
0002 11
0002 22
0004 22
0001 12
0001 13
0002 23
0004 43
0004 44
0005 51
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语句复杂了点。。
应该有个更好的方法。。
有个简单的
select distinct t1.* from table t1,table t2
where t1.userid=t2.userid and t1.memberid!=t2.memberid
order by t1.userid asc
谢谢大家的回复!!!!!!!!!!!!