具有自定义分组列的Oracle标识列

Question

我正在处理一个需求，需要在一行上填充一个唯一常量标识符，该行是经理行，在每个部门都是唯一的。我的表格结构是

CREATE
TABLE TEST_ORGANIZATION
(
EMPLOYEE_ID   NUMBER NOT NULL,
MANAGER_ID    NUMBER,
FIRST_NAME    VARCHAR2(256),
DEPARTMENT_ID VARCHAR2(28) NOT NULL,
UUID          VARCHAR2(28) ,
PRIMARY KEY(UUID)
);

此表包含.信息

员工1米哈尔员工3罗纳尔多员工4托马斯·员工5employee6employee7employee8employee9员工10

UUID	部门_ID	员工_ID	MANAGER_ID	FIRST_NAME
radmon1	财务	员工1		John B
radmon2	财务	员工2
radmon3	财务	员工1
radmon4	财务	员工1
radmon5	财务		百分比
radmon6	账户			Stacy
radmon7	账户		Jordan
radmon8	账户	employee 6	Micky
radmon9	账户	employee 6	作者
radmon10	账户	员工6	戈尔丹

试试这个：

SELECT d1.*,
1 AS f,
CASE WHEN manager_id is null then
RANK() OVER (partition by department_ID order by manager_id nulls first,employee_id)
end as sequenc
FROM   (
SELECT 'radmon1' AS UUID,'finance' AS DEPARTMENT_ID,'employee1' AS EMPLOYEE_ID,'' AS MANAGER_ID,'John B' AS    FIRST_NAME          from dual UNION ALL
SELECT 'radmon2','finance','employee2','employee1','Michal'     from dual UNION ALL
SELECT 'radmon3','finance','employee3','employee1','Ronaldo'    from dual UNION ALL
SELECT 'radmon4','finance','employee4','employee1','Thomas'     from dual UNION ALL
SELECT 'radmon5','finance','employee5','','Percey'         from dual UNION ALL
SELECT 'radmon6','account','employee6','','Stacy'              from dual UNION ALL
SELECT 'radmon7','account','employee7','','Jordan'            from dual UNION ALL
SELECT 'radmon8','account','employee8','employee6','Micky'      from dual UNION ALL
SELECT 'radmon9','account','employee9','employee6','Author'     from dual UNION ALL
SELECT 'radmon10','account','employee10','employee6','Gordan'   from dual
)d1;