本文来自CSDN博客:http://blog.csdn.net/huanghui22/archive/2007/05/03/1595166.aspx
有些时候我们希望得到指定数据中的前n列,示例如下:
得到每个部门薪水最高的三个雇员:
先创建示例表
create table emp
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key(empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key(deptno);
先看一下row_number() /rank()/dense_rank()三个函数之间的区别
select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3
rank() over (partition by deptno order by sal desc) rank, --1,1,3
dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2
结果如下:
10 5000.00 7839 1 1 1
10 2450.00 7782 2 2 2
10 1300.00 7934 3 3 3
20 3000.00 7788 1 1 1
20 3000.00 7902 2 1 1
20 2975.00 7566 3 3 2
20 1100.00 7876 4 4 3
20 800.00 7369 5 5 4
30 2850.00 7698 1 1 1
30 1600.00 7499 2 2 2
取每个部门的薪水前三位雇员:
select t.deptno,t.rank,t.sal from
(
select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3
rank() over (partition by deptno order by sal desc) rank, --1,1,3
dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2
) t
where t.rank<=3
结果如下:
10 1 5000.00
10 2 2450.00
10 3 1300.00
20 1 3000.00
20 1 3000.00
20 3 2975.00
30 1 2850.00
30 2 1600.00
30 3 1500.00
如果想输出成deptno sal1 sal2 sal3这种类型的格式
步骤一(decode):
select t.deptno,decode(row_number,1,sal) sal1,decode(row_number,2,sal) sal2,decode(row_number,3,sal) sal3 from
(
select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3
rank() over (partition by deptno order by sal desc) rank, --1,1,3
dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2
) t
where t.rank<=3
结果如下:
10 5000
10 2450
10 1300
20 3000
20 3000
20 2975
30 2850
30 1600
30 1500
步骤二(使用聚合函数去除null,得到最终结果):
select t.deptno,max(decode(row_number,1,sal)) sal1,max(decode(row_number,2,sal)) sal2,max(decode(row_number,3,sal)) sal3 from
(
select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3
rank() over (partition by deptno order by sal desc) rank, --1,1,3
dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2
) t
where t.rank<=3
group by t.deptno
结果如下:
10 5000 2450 1300
20 3000 3000 2975
30 2850 1600 1500
分享到:
相关推荐
ROW_NUMBER、RANK、DENSE_RANK 和 NTILE 排名函数 MS SQL 2005 中的排名函数包括 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE,这四个函数可以有效地分析数据并提供排序值。下面将详细介绍每个函数的用法和示例。 一、...
- `row_number`:为每一行生成一个唯一的序号,不受相同值的影响。 - `rank`:在考虑相同值的情况下分配序号,出现相同值时跳过序号。 - `dense_rank`:同样考虑相同值,但保证序号的连续性,不会跳过序号。 - `...
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER (ORDER BY SalesYTD DESC) AS 'RowNumber', s.SalesYTD, a.PostalCode FROM Sales.SalesPersons JOIN Person.Contact cons ON s.SalesPersonID = c.ContactID ...
在这篇文档中,我们将详细学习在Hive中如何进行分组取topN,以及如何使用row_number()、rank()和dense_rank()三种窗口函数进行数据排序和排名。 首先,Hive中的数据表创建和数据插入操作是数据查询和分析的前提。...
`ROW_NUMBER()`可以与`RANK()`和`DENSE_RANK()`进行比较。这些函数都可以用来给数据排序并分配等级,但是它们在处理并列的情况时有所不同: - **Rank**:如果两个或多个行具有相同的排序值,则这些行都将获得相同...
在SQL Server 2005中,`ROW_NUMBER()`、`RANK()`和`DENSE_RANK()`是三种常用的排名函数,它们在处理大数据集时非常有用,尤其在数据分析和报表生成方面。这些函数都可以帮助我们根据指定的条件对数据进行排序并赋予...
Oracle分析函数Rank、Dense_rank和row_number是用于处理数据集的高级工具,它们在数据库查询中发挥着关键作用,特别是在需要对数据进行排序和分组时。这三种函数都有各自的特点,适用于不同的业务场景。 1. **row_...
本文主要讲解SQL中的四大排名函数:ROW_NUMBER、RANK、DENSE_RANK以及NTILE,它们在数据分析和报表生成中起到关键作用。 1. ROW_NUMBER() ROW_NUMBER()函数用于为查询结果集中的每一行分配一个唯一的数字序列。这个...
“rank()、dense_rank()和row_number()的区别”文章的date
`rank()`和`dense_rank()`函数是`row_number()`的两个变体: - **rank()**:类似`row_number()`,但它在遇到相等的排序值时会产生跳跃。也就是说,如果有两个行在排序列上有相同的值,它们都会被赋予相同的排名,接...
`row_number()`, `rank()`, 和 `dense_rank()` 是三个重要的窗口函数,用于为查询结果集中的每一行分配唯一的序列号,常用于分组、排名或者实现分区排序。下面我们将详细探讨这三个函数的区别和用法。 1. `row_...
此外,`ROW_NUMBER()`函数还有其他窗口函数,如`RANK()`和`DENSE_RANK()`,它们在某些场景下可能会提供更灵活的解决方案。但在这里,我们主要关注`ROW_NUMBER()`在分页中的应用。 总结起来,`ROW_NUMBER()`函数在...
ROW_NUMBER()、RANK()和DENSE_RANK()是其中的三个关键函数,它们都用于对数据进行排序并分配唯一的序列号,但各有其特点和适用场景。以下是对这三个函数的详细解释: 1. ROW_NUMBER() ROW_NUMBER()函数为每一行提供...
SQL Server 2005 中提供了四个排名函数,分别是 row_number、rank、dense_rank 和 ntile。这四个函数的主要功能是为查询结果生成一个序号,用于实现查询指定范围的记录或实现分页功能。 一、row_number 函数 row_...