我有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子句在子查询生成之前正在评估,并且我也应该使用联接.如果有人可以帮助我或指出正确的方向,那将是令人难以置信的.
编辑:限制是错误的,修复它
解决方法:
完全不需要第一个子查询,因为您可以直接在当前表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