给定月份的所有记录和一个以前的记录(如果存在)SQL Oracle

ia2d9nvy  于 2023-08-04  发布在  Oracle
关注(0)|答案(1)|浏览(106)

我需要创建一个用户角色更改到Excel中的一年中的给定月份的总结报告。
用户输入:2023年4月
有两张table。表:角色

ROLE_ID | ROLE_NAME
1       | Role 1
2       | Role 2
3       | Role 3

字符串
表:USER_AUDIT

USER_ID | USER_NAME | ROLE_ID | CREATED_AT | UPDATED_AT | AUDIT_ACTION | AUDIT_DATE
1       | User 1    | 1       | <datetime> | <datetime> | Created      | '01-01-23'         
1       | User 1    | 2       | <datetime> | <datetime> | Updated      | '01-04-23' //Changed in April-2023
1       | User 1    | 3       | <datetime> | <datetime> | Updated      | '02-04-23' //Changed in April-2023
2       | User 2    | 1       | <datetime> | <datetime> | Created      | '01-01-23'         
2       | User 2    | 2       | <datetime> | <datetime> | Updated      | '01-02-23'
2       | User 2    | 3       | <datetime> | <datetime> | Updated      | '01-04-23' //Changed in April-2023
2       | User 2    | 2       | <datetime> | <datetime> | Updated      | '02-04-23' //Changed in April-2023
3       | User 3    | 2       | <datetime> | <datetime> | Created      | '01-03-23' 
4       | User 4    | 3       | <datetime> | <datetime> | Created      | '01-04-23' //Changed in April-2023


在上表中,仅用户1、2和4在2023年4月发生了一些变化。
报告(Excel):2023年4月的变更

USER_ID | USER_NAME | ACTIVITY                                      | CHANGED_AT
1       | User 1    | Updated role from Role 1 to Role 2 to Role 3  | <last-audit-date>
2       | User 2    | Updated role from Role 2 to Role 3 to Role 2  | <last-audit-date>
4       | User 4    | Created with role Role 3                      | <last-audit-date>


我可以在Java中处理报告的活动语句,但不能读取整个USER_AUDIT表,因为它可能很大。
如何获取给定月份的记录(在上述情况下为4月23日)以及每个用户的一个先前记录(如果存在),以便保留在记录中,指示从哪个现有角色进行角色更改?
需要从USER_AUDIT读取为:

USER_ID | USER_NAME | ROLE_ID | CREATED_AT | UPDATED_AT | AUDIT_ACTION | AUDIT_DATE
1       | User 1    | 1       | <datetime> | <datetime> | Created      | '01-01-23' //One previous record of user 1
1       | User 1    | 2       | <datetime> | <datetime> | Updated      | '01-04-23' //Changed in April-2023
1       | User 1    | 3       | <datetime> | <datetime> | Updated      | '02-04-23' //Changed in April-2023     
2       | User 2    | 2       | <datetime> | <datetime> | Updated      | '01-02-23' //One previous record of user 2
2       | User 2    | 3       | <datetime> | <datetime> | Updated      | '01-04-23' //Changed in April-2023
2       | User 2    | 2       | <datetime> | <datetime> | Updated      | '02-04-23' //Changed in April-2023
4       | User 4    | 3       | <datetime> | <datetime> | Created      | '01-04-23' //Changed in April-2023


我可以得到给定月份的记录,但不知道如何得到一个以上的每个用户以前的记录,如果存在。

select r.ROLE_NAME, ua.* 
FROM USER_AUDIT ua JOIN ROLE r ON ua.ROLE_ID = r.ROLE_ID 
WHERE AUDIT_DATE BETWEEN TO_DATE('2023/04/01 0:00:00', 'YYYY/MM/DD HH24:MI:SS') 
AND TO_DATE('2023/04/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS') 
ORDER BY ua.USER_ID asc, ua.AUDIT_DATE asc;


也可采用不同的方法

czq61nw1

czq61nw11#

您可以使用LEAD分析函数查找下一个值,并确保下一个值在月初之后,或者当前值在月底之前。

SELECT *
FROM   (
  select r.ROLE_NAME,
         ua.*,
         LEAD(audit_date) OVER (ORDER BY audit_date) AS next_audit_date 
  FROM   USER_AUDIT ua
         JOIN ROLE r
         ON ua.ROLE_ID = r.ROLE_ID 
)
WHERE  COALESCE(NEXT_AUDIT_DATE, AUDIT_DATE) >= DATE '2023-04-01'
AND    AUDIT_DATE <  DATE '2023-05-01'
ORDER BY
       ua.USER_ID asc,
       ua.AUDIT_DATE asc;

字符串

相关问题