sql - 如何优化嵌套查询？

Question

我能否以某种方式连接表并避免在以下 MySQL 查询中使用 distinct。invited_by_id 显示邀请此用户的用户 ID。

SELECT
    user1.id, count(distinct user2.id) AS theCount, count(distinct user3.id) AS theCount2
FROM
    users AS user1
LEFT OUTER JOIN
    users AS user2 ON user2.invited_by_id=user1.id
LEFT OUTER JOIN (
    SELECT id, invited_by_id FROM users WHERE signup_date >= NOW() - INTERVAL 30 DAY
) AS user3 ON user3.invited_by_id=user1.id
GROUP BY user1.id;

Answer 1

我在这里假设您正在尝试统计某个用户被邀请的次数以及该用户在过去 30 天内被邀请的次数。

在这种情况下，您可以使用简单的条件总和进行查询:

select user1.id, count(user2.id) as tehCount, sum(user2.signup_date >= NOW() - INTERVAL 30 DAY) as theCount2
from users as user1
left outer join users as user2 on user2.invited_by_id = user1.id
group by user1.id

如果 theCount2 中的空值会成为问题，请使用合并作为:

coalesce(sum(user2.signup_date >= NOW() - INTERVAL 30 DAY), 0)