Oracle 定长范围分组统计

最新推荐文章于 2022-07-14 17:27:22 发布

fighting-ff

最新推荐文章于 2022-07-14 17:27:22 发布

阅读量950

点赞数

分类专栏： Oracle 文章标签：递归 with 分组统计 connect by

本文链接：https://blog.csdn.net/i99121570/article/details/16859359

版权

本文介绍了如何在Oracle数据库中进行定长范围分组统计，并通过示例展示了使用WITH子句和递归查询（CONNECT BY）来处理数据。首先，展示了如何根据一定条件进行分组，然后演示了创建DEP表并插入数据，最后通过START WITH和CONNECT BY查询展示部门的层级结构。

摘要由CSDN通过智能技术生成

WITH T AS (
SELECT 1 as N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 4 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELECT 10 N FROM DUAL UNION ALL
SELECT 11 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 19 N FROM DUAL UNION ALL
SELECT 20 N FROM DUAL
)

select n, row_number() over(order by n) rn from T;

N RN
------- ----------
1 1
3 2
4 3
7 4
10 5
11 6
12 7
12 8
19 9
20 10

WITH T AS (
SELECT 1 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 4 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELEC