使用：Oracle SQL Developer（版本21.4.2.018）-但系统使用LextEdit更新（我不使用）
说明

首先，我对SQL非常陌生。我试图创建一个表，显示何时在我们的系统中创建最终用户，以及何时从我们的系统中删除他们。第一次他们的ID似乎被添加到'角色'中，在第3列中，是当他们被添加到系统。当它说“从所有角色中删除”是当他们被删除。我正在从2个不同的表拉。一个是我们的审计，只能追溯到大约10天，另一个是存档，可以追溯到时间的开始（Hello World）。

问题

我试图找到最好的方法来找到第一次用户ID显示在第3列（Upd_Des），措辞为“Added usr_id = USERS ID to role_id blah”。在整个表中多次显示用户ID。我不想具体说明用户ID名称，因为第3列中会有100个不同的ID。我不知道我需要什么功能来实现这一点.此外，我不知道如果我甚至写这个正确，但它似乎是拉所有的信息，我需要得到它去.信息：第1列“UpdDate”是更新帐户的日期第2列“Usr”是对帐户进行更改的人员（系统管理员）第3列“UpdDes”是对最终用户帐户更改的说明

我所拥有的

Select Upd_Date, Usr, Upd_Des
From (
SELECT auddte Upd_Date, usr_id Usr, exec_cmd Upd_Des
FROM arc_sys_audit
WHERE aud_typ = 'I'
AND appl_id <> 'LEXTEDIT'
AND appl_id <> 'srvcmdopr'
AND appl_id <> 'srvcmdmnt'
AND exec_cmd LIKE ('%from all roles%')
AND auddte >= sysdate -365
UNION all 
SELECT auddte Upd_Date, usr_id Usr, exec_cmd Upd_Des
FROM arc_sys_audit
WHERE aud_typ = 'I'
AND appl_id <> 'LEXTEDIT'
AND appl_id <> 'srvcmdopr'
AND appl_id <> 'srvcmdmnt'
AND exec_cmd LIKE ('%Added usr_id = % to role_id%')
AND auddte >= sysdate -365
union all
SELECT auddte Upd_Date, usr_id Usr, exec_cmd Upd_Des
FROM sys_audit
WHERE aud_typ = 'I'
AND appl_id <> 'LEXTEDIT'
AND appl_id <> 'srvcmdopr'
AND appl_id <> 'srvcmdmnt'
AND exec_cmd LIKE ('%from all roles%')
AND auddte >= sysdate -365
UNION all 
SELECT auddte Upd_Date, usr_id Usr, exec_cmd Upd_Des
FROM sys_audit
WHERE aud_typ = 'I'
AND appl_id <> 'LEXTEDIT'
AND appl_id <> 'srvcmdopr'
AND appl_id <> 'srvcmdmnt'
AND exec_cmd LIKE ('%Added usr_id = % to role_id%')
AND auddte >= sysdate -365)
order by Upd_Date desc

结果-有1000行，但对于这个例子，我只给予你20行。

|Upd_Date     |Usr          |Upd_Des
--------------------------------------------------------------------------
|17-APR-23  |SYSADMIN1  |Added usr_id = EMPLOYEE1 to role_id = JNJ Quality
|17-APR-23  |SYSADMIN1  |Added usr_id = EMPLOYEE1 to role_id = REPORT_USER
|12-APR-23  |SYSADMIN2  |Added usr_id = EMPLOYEE2 to role_id = Warehouse Operator
|10-APR-23  |SYSADMIN1  |Removed user_id = EMPLOYEE3 from all roles
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = RECEIVING
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = REPORT_ADMIN
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = LITEWM
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = GENERAL_MANAGER
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = QUALITY_MANAGER
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = OPS_SUPERVISOR
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = ORDER
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = WORKFLOW
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = WM_USER
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = QUALITY_ASSOCIATE
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = OPERATIONS_MANAGER
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = WORKMANAGER
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = SHIPPING_MANAGER
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = CSRV

我想让你知道

|Upd_Date     |Usr          |Upd_Des
--------------------------------------------------------------------------
|17-APR-23  |SYSADMIN1  |Added usr_id = EMPLOYEE1 to role_id = REPORT_USER
|12-APR-23  |SYSADMIN2  |Added usr_id = EMPLOYEE2 to role_id = Warehouse Operator
|10-APR-23  |SYSADMIN1  |Removed user_id = EMPLOYEE3 from all roles
|07-APR-23  |SYSADMIN3  |Added usr_id = EMPLOYEE4 to role_id = CSRV

任何帮助都非常感谢！