这个sql怎么写？

[jxauyhj]

有一表结构如下：
id value
1 60
1 30
1 44
2 62
2 55
3 64
3 52
3 34
3 30
我想把 筛选出这样的结果集 当输入值为i时 筛选出id 为1，2，3。。各自对应的第i条记录，没有则为0
怎么做？ 帮帮忙

[GerryYang]

row_number() r over(partition by id order by id)
其实蛮容易理解的.
如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn<=3
看了上面的例子应该明白的

[子陌红尘]

select
c.id ,
c.value
from
(select a.id,min(a.newID) as newID from
(select rownum as newID , id from 表名 order by id) a
group by a.id) b
inner join
(select rownum as newID , id from 表名 order by id) c
on
b.id = c.id and c.newID = (b.newID + i - 1)
order by
c.id

[ahalf]

再加一个字段i不就结了

[jxauyhj]

加了一个字段了！没搞定，帮忙帮忙

[flamingo10000]

select t1.id,t1.value from (select id ,value ,row_number()over(partition by id order by id)ranks from test)t1 inner join (select '4' i from dual) t2 on t1.ranks=t2.i

[GerryYang]

select a.id,a.value from
(select id ,value ,row_number() r over(partition by id order by id) from test) a
where a.r = &i;
输入你要的值就可以了.

[jxauyhj]

高手高手啊！这些语句我都看不懂！
不过有用了，谢谢，： flamingo10000()， GerryYang(轻尘) 能详细讲下吗

[soniczck]

都是用到了分析函数，好象ORACLE815以上才持，你可以搜索一下分析函数看看。