Sql 查询以查找彼此相隔 5 分钟内发生的一系列日期? [英] Sql Query to find A series of dates that occur within 5 minutes of each other?

问题描述

这是 Sql Server 2008.我有一组看起来像这样的数据:

This is Sql Server 2008. I've got a set of data that looks something like this:

Table UserAchievement
   id
   userId
   achievementId
   completedDate

当用户获得奖励时，奖励和用户将与日期一起记录.我想要的是一个查询，该查询查找同一用户在 5 分钟内获得的一组 3 项成就.关于如何实现这一点的任何想法?

When a user earns an award, the award and user is logged along with a date. What I'd like is a query that finds a set of 3 achievements that were earned within 5 minutes of each other by the same user. Any thoughts on how to accomplish this?

提前致谢-

楚

推荐答案

我博客中的这篇文章更详细地解释了解决方案:

This article in my blog explains the solution in more detail:

• 在一段时间内查找集框架

SELECT  *
FROM    UserArchievement uf
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    UserArchievement un
        WHERE   un.userId = uf.userID
                AND un.completedDate BETWEEN DATEADD(minute, -5, uf.completedDate) AND DATEADD(minute, 5, uf.completedDate)
                AND un.id <> uf.id
        )

如果您想在 5 分钟内选择所有 N 组奖项，请使用:

If you want to select all sets of N awards within 5 minutes, use this:

SELECT  DISTINCT ue.id
FROM    UserArchievement uf
JOIN    UserArchievement ue
ON      ue.userID = uf.userID
        AND ue.completedDate BETWEEN uf.completedDate AND DATEADD(minute, 5, uf.completedDate)
WHERE   (
        SELECT  COUNT(*)
        FROM    UserArchievement un
        WHERE   un.userId = uf.userID
                AND un.completedDate BETWEEN uf.completedDate AND DATEADD(minute, 5, uf.completedDate)
        ) = 3

将 3 替换为您需要的任意数量的奖励.

Replace 3 with any number of awards in the set you need.

这篇关于Sql 查询以查找彼此相隔 5 分钟内发生的一系列日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！