如何修改SQL查询，获取满足特定Audit动作条件的两类用户？

Your current query works for the first group (users with 64/65 audits older than 6 months) but misses the second group because it uses an INNER JOIN to a subquery that only includes users who have those audit records. To include users with no such audits at all, we need to adjust the join type and add a condition to capture both scenarios.

Here's the revised query:

DECLARE @monthsInactiveFor INT = -6;

SELECT 
    USR.fullname,
    audit_summary.last_logged_in_date
FROM systemuser AS USR
LEFT JOIN (
    -- Get the latest 64/65 audit date for each user who has such records
    SELECT 
        AB.objectid AS USERID, 
        MAX(AB.createdon) AS last_logged_in_date
    FROM auditbase AS AB
    WHERE AB.action IN (64, 65)
    GROUP BY AB.objectid
) AS audit_summary ON USR.systemuserid = audit_summary.USERID
WHERE 
    USR.isdisabled = 0 
    AND USR.createdon <= DATEADD(month, @monthsInactiveFor, GETDATE()) 
    AND USR.accessmode = 0
    -- Include users either with no 64/65 audits, or latest audit is older than 6 months
    AND (
        audit_summary.last_logged_in_date IS NULL 
        OR audit_summary.last_logged_in_date <= DATEADD(month, @monthsInactiveFor, GETDATE())
    )
ORDER BY USR.fullname;

Key Changes Explained: #

• LEFT JOIN Instead of INNER JOIN: This ensures all users from systemuser that meet your base filters (isdisabled=0, createdon older than 6 months, accessmode=0) are included, even if they have no matching audit records.
• Audit Summary Subquery: This subquery isolates the logic for finding the most recent 64/65 audit per user, making the main query cleaner and easier to maintain.
• Dual Condition in WHERE Clause: The OR condition explicitly captures both user groups:
  • audit_summary.last_logged_in_date IS NULL: Users who have never had an audit record with action 64 or 65.
  • audit_summary.last_logged_in_date <= ...: Users whose latest 64/65 audit is older than the 6-month threshold.

If your schema requires using systemuserbase instead of systemuser for core user data, you can adjust the main query to reference systemuserbase and join to systemuser for the fullname field—just keep the core logic of the LEFT JOIN and dual filter condition intact.

内容的提问来源于stack exchange，提问作者dynamicallyCRM