用分析函数实现比较简单。 row_number,rank,dense_rank SELECT ename, top5, last5 FROM (SELECT ename, ROW_NUMBER () OVER (ORDER BY sal DESC) top5, ROW_NUMBER () OVER (ORDER BY sal ASC) last5 FROM emp) WHERE top5 <= 5 OR last5 <= 5
SELECT emp.雇员name from emp where rownum <=5 order by 工资 asc -- 后五名 union all SELECT emp.雇员name from emp where rownum <=5 order by 工资 desc --前五名
select 姓名,工资 from (SELECT 姓名,工资 from emp order by 工资) where rownum <=5 union all select 姓名,工资 from (SELECT 姓名,工资 from emp order by 工资 desc) where rownum <=5
试试这个…… select * from (SELECT emp.雇员 name from emp order by 工资 asc) where rownum <=5 -- 后五名 union all select * from (SELECT emp.雇员 name from emp order by 工资 desc) where rownum <=5 --前五名
[Quote=引用 3 楼 thcthc2 的回复:] 这个我试了是好像有错。联合查询有问题吧sorry,刚才没怎么考虑select* from (select * from tbl1 t order by cast asc) where rownum<=2 union all select* from (select * from tbl1 t order by cast desc) where rownum<=2
row_number,rank,dense_rank
SELECT ename, top5, last5
FROM (SELECT ename,
ROW_NUMBER () OVER (ORDER BY sal DESC) top5,
ROW_NUMBER () OVER (ORDER BY sal ASC) last5
FROM emp)
WHERE top5 <= 5 OR last5 <= 5
where rownum <=5
order by 工资 asc -- 后五名
union all
SELECT emp.雇员name from emp
where rownum <=5
order by 工资 desc --前五名
(SELECT 姓名,工资 from emp order by 工资)
where rownum <=5
union all
select 姓名,工资 from
(SELECT 姓名,工资 from emp order by 工资 desc)
where rownum <=5
select * from (SELECT emp.雇员 name from emp
order by 工资 asc)
where rownum <=5 -- 后五名
union all
select * from (SELECT emp.雇员 name from emp
order by 工资 desc)
where rownum <=5 --前五名
这个我试了是好像有错。联合查询有问题吧sorry,刚才没怎么考虑select* from (select * from tbl1 t
order by cast asc)
where rownum<=2
union all
select* from (select * from tbl1 t
order by cast desc)
where rownum<=2