不重复每月的SQL(Non repeating monthly SQL)

不重复每月的SQL(Non repeating monthly SQL)

我有一个查询，我想返回登录该月的用户数量，而不在下个月重复记录。

如果用户在四月和五月登录，它只会显示四月的一个记录。 这是我迄今为止所拥有的。

SELECT DISTINCT (a.userid), EXTRACT(MONTH FROM a.loginTime) as month FROM login_audit a LEFT JOIN user u on u.userid = a.userid WHERE a.loginTime <= '2012-12-31 11:59:59' AND a.loginTime >= '2012-01-01 00:00:00' GROUP BY month

到目前为止，记录正在返回

userid month 1 1 2 1 1 2 3 2

在这种情况下，用户1即将来临一月和Februray。 我希望它能省略那条记录。 要么就是要积累。 像这样：

或

userid month 1 1 2 1 3 2

要么

userid month 1 1 2 1 1 2 2 2 3 2

我希望这是有道理的。 如果您需要进一步澄清，请问任何问题。 非常感谢！

I have a query in which I would like to return the number of users who have logged in for the month without repeating the record in the next month.

If a user has logged in April and May, it only shows one record for April. This is what I have so far.

SELECT DISTINCT (a.userid), EXTRACT(MONTH FROM a.loginTime) as month FROM login_audit a LEFT JOIN user u on u.userid = a.userid WHERE a.loginTime <= '2012-12-31 11:59:59' AND a.loginTime >= '2012-01-01 00:00:00' GROUP BY month

So far the records are returning

userid month 1 1 2 1 1 2 3 2

In this scenario, user 1 is coming up for both January and Februray. I would like it to ommit that record. Either that or have it accumulated. Like so:

Either

userid month 1 1 2 1 3 2

Or

userid month 1 1 2 1 1 2 2 2 3 2

I hope this made sense. Please ask me anything if you'd like any further clarifications. Thanks a lot!

最满意答案

没有看到你需要的桌面用户...

对于第一个“想要的场景”：

SELECT a.userid, MIN(EXTRACT(MONTH FROM a.loginTime)) as month FROM login_audit a WHERE a.loginTime <= '2012-12-31 11:59:59' AND a.loginTime >= '2012-01-01 00:00:00' GROUP BY a.userid

Don't see where you need table user...

For first "wanted scenario" :

SELECT a.userid, MIN(EXTRACT(MONTH FROM a.loginTime)) as month FROM login_audit a WHERE a.loginTime <= '2012-12-31 11:59:59' AND a.loginTime >= '2012-01-01 00:00:00' GROUP BY a.userid