mysql - SQL 查询 :Repeated Occurrences of user-login after (and including) first occurrence

Question

我需要在给定日期之后从 loginhistory 表中查找重复出现的用户登录。我尝试了以下查询，但它给出了零行。 loginhistory 表有两列，即 userkeyid 和 datecreated。以下是我正在使用的查询:

问题陈述:

*用户在2013-10-26或之后首次登录，并且在首次登录后，应显示迄今为止所有重复出现的情况。对于 2013-10-26 或之后首次登录的所有用户，都应该发生这种情况 *

SELECT
    FirstOccurenceHistory.firsttimeuserkeyid userkeyid,
    firstOccurrenceDate,
    DATE(fullhistory.dateCreated) repeatedOn
FROM (
      SELECT DATE(dateCreated) firstOccurrenceDate,
          COUNT(1) n,
          userkeyid firsttimeuserkeyid
      FROM
          loginhistory
      GROUP BY userkeyid
      HAVING COUNT(userkeyid) = 1 and firstOccurrenceDate >= DATE('2013-10-26')
     )
    as FirstOccurenceHistory
INNER JOIN loginhistory fullhistory
    ON fullhistory.userkeyid = FirstOccurenceHistory.firsttimeuserkeyid
    AND DATE(fullhistory.dateCreated) > FirstOccurenceHistory.firstOccurrenceDate;

Answer 1

你过度设计了它。怎么样:

select f1, f2, etc, count(*) records
from loginhistory join other tables as necessary
where datecreated >= date('2013-10-26')
group by f1, f2, etc
having count(*) > 1

编辑从这里开始

要将其限制为在该日期或之后首次登录的用户，请将以下内容添加到上述查询的 from 子句中

 join (select useridkey userid, min(datecreated) mindate
 from loginhistory
 group by useridkey
 having min(datecreated) >= date('2013-10-26')
 ) temp on userid = useridkey