SQL> select * from aa; CLASS NAME SCORE ---------- -------- ---------- 1 aa 90 1 bb 80 1 猪八戒 60 1 孙悟空 55 1 唐僧 15 1 沙和尚 5 1 楼主 99已选择7行。select a.class,a.name,a.score from (select aa.*,row_number() over(partition by class order by score) mc from aa) a where a.mc <= 3 union all select a.class,a.name,a.score from (select aa.*,row_number() over(partition by class order by score desc) mc from aa) a where a.mc <= 3 / CLASS NAME SCORE ---------- -------- ---------- 1 沙和尚 5 1 唐僧 15 1 孙悟空 55 1 楼主 99 1 aa 90 1 bb 80已选择6行。
或者select * from (select * from test order by col) where rownum<=3 union select * from (select * from test order by col) where rownum>=3
修改 select * from (select * from p_test Where ljjc>0 order by col) where rownum<=3 union select t.* from (select * from p_test Where ljjc>0 order by col Desc) t Where Rownum<=3
select no,a.* from (select rownum no,tb.* from tb) where no<=3 or no>=(select count(*)-3 from tb);
--前面别名用错了select no,a.* from (select rownum no,tb.* from tb) a where no<=3 or no>=(select count(*)-3 from tb);
如果是分组的前3名和后3名select no,a.* from (select rownum no,col1,col2,...,coln from (select col1,col2,...,coln from tb group by col1,col2,...,coln)) a where no<=3 or no>=(select count(*)-3 from tb);其中col1为排名字段由于我的版本是805,不能在子句中用ORDER BY故用group by。
select top 3 * from p_test order by col acs union all select top 3 * from p_test order by col desc
---------- -------- ----------
1 aa 90
1 bb 80
1 猪八戒 60
1 孙悟空 55
1 唐僧 15
1 沙和尚 5
1 楼主 99已选择7行。select a.class,a.name,a.score from
(select aa.*,row_number()
over(partition by class order by score) mc from aa) a
where a.mc <= 3
union all
select a.class,a.name,a.score from
(select aa.*,row_number()
over(partition by class order by score desc) mc from aa) a
where a.mc <= 3
/ CLASS NAME SCORE
---------- -------- ----------
1 沙和尚 5
1 唐僧 15
1 孙悟空 55
1 楼主 99
1 aa 90
1 bb 80已选择6行。
union
select * from (select * from test order by col) where rownum>=3
select * from (select * from p_test Where ljjc>0 order by col) where rownum<=3
union
select t.* from (select * from p_test Where ljjc>0 order by col Desc) t Where Rownum<=3
union all
select top 3 * from p_test order by col desc