我认为在主查询之前计算计数并将其保存在temp表中有助于提高效率，还可以使用inner join关键字。请尝试下面的查询。可以设置首选项而不是空值（而不是0）：

CREATE TEMPORARY TABLE t1 (SELECT count(apd.id) as Total
                           FROM apple_purchase_details as apd 
                           INNER JOIN users as u on u.userid = apd.id 
                           GROUP BY u.userid 
                           HAVING  count(apd.id) < 5);

SELECT
u.userid, u.lastopen, r.auto_renew_status, r.product_id, r.is_trial_period, 
(case when (select Total from t1) is null then 0 else (Select Total from t1) end) as total
FROM users as u, receipt as r 
INNER JOIN receipt as r on  r.id = u.userid and r.is_trial_period = 'false' and r.auto_renew_status = 0
ORDER BY u.lastopen

它回来了 NULL 哪里 total 大于5，因为您指定了 HAVING count(apd.id) < 5 ，这意味着子查询在大于5时将不返回任何内容，从而导致 NULL 特定行的值。
如果你不想这种行为，删除 HAVING 子查询中的约束。
这个查询从多个表中检索数据，以此类推 lastopen 在 users 帮不了什么忙。我将在中使用的列上创建索引
JOIN ON 条款。
更新
要获得所需的结果，请尝试：

SELECT
u.userid, u.lastopen, 
r.auto_renew_status, r.product_id, r.is_trial_period, 
FROM users as u
JOIN receipt as r ON u.userid = r.id
JOIN (
    SELECT id, count(apd.id) FROM apple_purchase_details
    GROUP BY id
    HAVING apd.id > 5
) as apd ON apd.id = u.userid
WHERE r.is_trial_period = 'false' and r.auto_renew_status = 0

当appid大于或等于5时，您的子查询将返回null，因此您将获得null。如果您不想使用null，请使用条件case..when..end将null替换为所需的值。