哪位大哥帮我一个忙啊,
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;
针对上述语句,我想取分组后每组的前三条记录,咋样修改一下
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