mysql - 如果所有类(class)都由用户在mysql中完成，则统计用户

Question

我有 2 个表 5r63P4FZ_users 和 5r63P4FZ_2_learndash_user_activity，在这个表 5r63P4FZ_2_learndash_user_activity 中所有的类(class)状态都会在那里，这里有用户 ID 36，分配了2门类(class)，其activity_id为105和19，这2门类(class)为用户ID = 36，我只想在完成该类(class)时才计算该用户，这意味着它的 activity_status = 1，但现在在我的表中它的状态是 1 和 0，这意味着用户没有完成这两个类(class)，所以我想将该用户计为 0，我已经为此添加了我的查询，但它给了我计数 1 而不是 0，任何人都可以帮我解决这个问题，这是我的表和查询

5r63P4FZ_users 

5r63P4FZ_2_learndash_user_activity

Query : 

SELECT COUNT(DISTINCT u.ID) as total
FROM 5r63P4FZ_users AS u
INNER JOIN 5r63P4FZ_2_learndash_user_activity as ua 
ON u.ID = ua.user_id AND ua.activity_status = "1" AND ua.activity_type = 'course'
WHERE u.ID = 36

Answer 1

SELECT COUNT(*) FROM (
    SELECT DISTINCT 5r63P4FZ_users.user_id FROM
        5r63P4FZ_users INNER JOIN 5r63P4FZ_2_learndash_user_activity
        ON 5r63P4FZ_users.user_id = 5r63P4FZ_2_learndash_user_activity.user_id
        AND activity_status = 1 and activity_type = 'course'
        WHERE 5r63P4FZ_users.user_id NOT IN (
            SELECT DISTINCT user_id FROM 5r63P4FZ_2_learndash_user_activity
                WHERE activity_status = 0 and activity_type = 'course'
            )
    )
;