哪位大哥帮我一个忙啊,
SELECT serv_id,orig_nbr,term_nbr,COUNT(*) cnt
FROM ur_fix_local_200811_t@jfdb
GROUP BY serv_id,orig_nbr,term_nbr;
针对上述语句,我想取分组后每组的前三条记录,咋样修改一下
SELECT serv_id,orig_nbr,term_nbr,COUNT(*) cnt
FROM ur_fix_local_200811_t@jfdb
GROUP BY serv_id,orig_nbr,term_nbr;
针对上述语句,我想取分组后每组的前三条记录,咋样修改一下
解决方案 »
- 关于java与oracle存储过程之间的问题
- 是 sqlldr的问题
- oracle 格式化输出问题---急
- 游标的更新 困惑
- 序列除了用Currval知道序列號,還有什么方式?
- v$nls_parameters 和 v_$nls_parameters ???
- 如何输出一个数据库中视图的脚本.
- 在对视图查询时提示 ORA-01031: 权限不足
- 用PL/SQL怎么把一个RAW(4)的变量拆成一个高两字节的RAW(2)一个低两字节的RAW(2)
- 急!!!!!ORACLE9IR2客户端在P4机子上安装,也有问题吗?
- 如何用游标加存储过程将两个结构相同数据不同的表数据进行互换,如A,B两表,高手给个例子
- 在oracle中如何批量修改字体中的某个词
select * from (
select serv_id,orig_nbr,term_nbr, dense_rank() over(partition by serv_id,orig_nbr,term_nbr) rn
) where rn <=3
SELECT serv_id,orig_nbr,term_nbr, row_number()over(partition by serv_id,orig_nbr,term_nbr order by serv_id desc) as rn
FROM ur_fix_local_200811_t@jfdb
) where rn<=3
serv_id1,orig_nbr1,term_nbr1,10
serv_id1,orig_nbr1,term_nbr1,9
serv_id1,orig_nbr1,term_nbr1,8
serv_id2,orig_nbr2,term_nbr2,20
serv_id2,orig_nbr2,term_nbr2,19
serv_id2,orig_nbr2,term_nbr2,18
SELECT serv_id,orig_nbr,term_nbr,
row_number()over(partition by serv_id,orig_nbr,term_nbr order by serv_id) as rn,
row_number()over(partition by serv_id,orig_nbr,term_nbr order by serv_id desc) as rn1
FROM ur_fix_local_200811_t@jfdb
) where rn1<=3
select * from(--选择你的你需要的字段
SELECT row_number()over(partition by serv_id,orig_nbr,term_nbr order by serv_id desc) as rn,t.* FROM ur_fix_local_200811_t@jfdb t
) where rn<=3
/*
--test data:
a01
10
9
8
7
20
19
18
17
30
29
28
27
26--result:
10
9
8
20
19
18
30
29
28
--sql:
select a01 from
(
select a01,row_number() over(partition by a02 order by a01 desc) rn from
(
select a01,nvl(nvl(case when a01 between 1 and 10 then 1 end,case when a01 between 11 and 20 then 2 end),case when a01 between 21 and 30 then 3 end) a02
from a
))
where rn<4--sql result:
10
9
8
20
19
18
30
29
28
*/
serv_id1,orig_nbr1,term_nbr1,10
serv_id1,orig_nbr1,term_nbr2,9
serv_id1,orig_nbr1,term_nbr3,8
serv_id2,orig_nbr2,term_nbr4,20
serv_id2,orig_nbr2,term_nbr5,19
serv_id2,orig_nbr2,term_nbr6,18