Oracle SQL分析函数row_number()获取前n条记录、去除重复记录

分析函数ROW_NUMBER应该是大家使用非常多的分析函数了。

常用来实现获取前n条，前1条记录、去除重复等需求。

下面根据官方文档的内容做下简单的介绍：

语法：

示例：
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
按照department_id进行分组，组内根据salary字段进行降序排序，然后每行返回一个从1开始的唯一值

作用：

1.ROW_NUMBER()是个分析函数，为组内的每行返回一个从1开始的唯一值，order by 子句（不能省略）指定了排序的字段。

2.一般情况下，先使用ROW_NUMBER()将每组记录排序后作为子查询，在外层限制子查询ROW_NUMBER()返回的值，可以轻松实现top-N, bottom-N, and inner-N等需求。

3.top-1，也就是一般对记录进行去重，限制每组的ROW_NUMBER()=1，也就是保留按排序条件的第1条，这样就实现了去重的目的

例子：

1.返回每个部门工资排名前三的员工，如果部门内的员工数为n<3,那么就返回前n名员工

SELECT department_id, first_name, last_name, salary
FROM
(
SELECT
department_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
FROM employees
)
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;

SELECT sales_2000.channel_desc, sales_2000.prod_name,
sales_2000.amt amt_2000,
top_5_prods_1999_year.amt amt_1999,
sales_2000.amt
- top_5_prods_1999_year.amt amt_diff
FROM
/* The first subquery finds the 5 top-selling products per channel in year 1999. */
(SELECT channel_desc, prod_name, amt
FROM
(
SELECT channel_desc, prod_name, sum(amount_sold) amt,
ROW_NUMBER () OVER (PARTITION BY channel_desc
ORDER BY SUM(amount_sold) DESC) rn
FROM sales, times, channels, products
WHERE sales.time_id = times.time_id
AND times.calendar_year = 1999
AND channels.channel_id = sales.channel_id
AND products.prod_id = sales.prod_id
GROUP BY channel_desc, prod_name
)
WHERE rn <= 5
) top_5_prods_1999_year,
/* The next subquery finds sales per product and per channel in 2000. */
(SELECT channel_desc, prod_name, sum(amount_sold) amt
FROM sales, times, channels, products
WHERE sales.time_id = times.time_id
AND times.calendar_year = 2000
AND channels.channel_id = sales.channel_id
AND products.prod_id = sales.prod_id
GROUP BY channel_desc, prod_name
) sales_2000
WHERE sales_2000.channel_desc = top_5_prods_1999_year.channel_desc
AND sales_2000.prod_name = top_5_prods_1999_year.prod_name
ORDER BY sales_2000.channel_desc, sales_2000.prod_name
;
CHANNEL_DESC
PROD_NAME
AMT_2000
AMT_1999
AMT_DIFF
--------------- --------------==-------------------------------- ---------- ---------- ----------
Direct Sales
17" LCD w/built-in HDTV Tuner
628855.7 1163645.78 -534790.08
Direct Sales
Envoy 256MB - 40GB
502938.54
843377.88 -340439.34
Direct Sales
Envoy Ambassador
2259566.96 1770349.25
489217.71
Direct Sales
Home Theatre Package with DVD-Audio/Video Play
1235674.15 1260791.44
-25117.29
Direct Sales
Mini DV Camcorder with 3.5" Swivel LCD
775851.87 1326302.51 -550450.64
Internet
17" LCD w/built-in HDTV Tuner
31707.48
160974.7 -129267.22
Internet
8.3 Minitower Speaker
404090.32
155235.25
248855.07
Internet
Envoy 256MB - 40GB
28293.87
154072.02 -125778.15
Internet
Home Theatre Package with DVD-Audio/Video Play
155405.54
153175.04
2230.5
Internet
Mini DV Camcorder with 3.5" Swivel LCD
39726.23
189921.97 -150195.74
Partners
17" LCD w/built-in HDTV Tuner
269973.97
325504.75
-55530.78
Partners
Envoy Ambassador
1213063.59
614857.93
598205.66
Partners
Home Theatre Package with DVD-Audio/Video Play
700266.58
520166.26
180100.32
Partners
Mini DV Camcorder with 3.5" Swivel LCD
404265.85
520544.11 -116278.26
Partners
Unix/Windows 1-user pack
374002.51
340123.02
33879.49
15 rows selected.

包含ROW_NUMBER()的sql作为子查询，外面套一层，然后限制rn

以上内容来自Oracle官方文档：

Oracle® Database SQL Language Reference
11g Release 2 (11.2)
E26088-03

最后

以上就是整齐白猫为你收集整理的Oracle SQL分析函数row_number()获取前n条记录、去除重复记录的全部内容，希望文章能够帮你解决Oracle SQL分析函数row_number()获取前n条记录、去除重复记录所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。