mysql - 'IN/ALL/ANY' 子查询中的未知列

Question

我有 2 个表:members 和 member_logs。

成员可以属于成员表中的组。给定一个日期范围和一个组，我试图弄清楚如何获得成功登录次数最多的 10 天。到目前为止，我所拥有的是一个巨大的子查询恐怖巢穴。

SELECT count(member_id) AS `num_users`,
DATE_FORMAT(`login_date`,'%Y-%m-%d') AS `reg_date` 
FROM member_logs 
WHERE `login_success` = 1 
and `reg_date` IN 
    (SELECT DISTINCT DATE_FORMAT(`login_date`,'%Y-%m-%d') AS `reg_date` 
     FROM member_logs 
     WHERE `login_success` = 1 
     and (DATE_FORMAT(`login_date`,'%Y-%m-%d') BETWEEN '2012-02-25' and '2014-03-04'))
and `member_id` IN 
    (SELECT `member_id` 
     FROM members 
     WHERE `group_id` = 'XXXXXXX' 
     and `deleted` = 0) 
ORDER BY `num_users` desc 
LIMIT 0, 10

据我了解发生的事情是 WHERE 子句在子查询生成之前进行评估，并且我也应该使用连接。如果有人能帮助我或为我指明正确的方向，那就太棒了。

编辑:限制错误，已修复

Answer 1

第一个子查询完全没有必要，因为你可以直接在当前表member_logs中按日期过滤。我也更喜欢为第二个子查询使用 JOIN。那么您缺少的是按日期(天)分组。

像下面这样的查询(未测试)将完成您想要的工作:

SELECT COUNT(ml.member_id) AS `num_users`, 
  DATE_FORMAT(`login_date`,'%Y-%m-%d') AS `reg_date` 
FROM member_logs ml
INNER JOIN members m ON ml.member_id = m.member_id
WHERE `login_success` = 1 
  AND DATE_FORMAT(`login_date`,'%Y-%m-%d') BETWEEN '2012-02-25' AND '2014-03-04'
  AND `group_id` = 'XXXXXXX' 
  AND `deleted` = 0 
GROUP BY `reg_date`
ORDER BY `num_users` desc 
LIMIT 10