如何让用户在过去3个小时内在Server中获得4个新的关注者？

Question

我想写一个查询：“如果一个用户在过去3个小时内获得了4个新的关注者，那么推送通知中会写一些类似于”xyz，abc和其他18个用户跟踪了您“，但是如何做到这一点呢？

这里是我在Server中的表：

用户表：

id | Username | datetime
---+----------+-------------------------
1    xyz        2017-05-03 15:52:59.750
2    abd        2017-05-02 15:52:59.743
3    sdd        2017-05-02 15:52:59.737
4    rvv        2017-05-02 15:52:59.727
5    dsa        2017-05-01 15:52:59.657
6    edr        2017-05-01 15:52:59.647
7    edc        2017-05-01 15:52:59.617
8    wer        2017-05-01 15:52:59.550 

这是我的userFollower表：

id | userid | FollowerId | Datetime
---+--------+------------+--------------------------
 1     1        5           2017-05-03 15:52:58.340
 2     1        2           2017-05-03 15:52:58.333
 3     1        8           2017-05-03 15:52:58.323
 4     1        7           2017-05-03 13:52:58.123
 5     1        6           2017-05-03 12:52:57.037

我在我的存储过程中编写了这个查询，但是我无法获得正确的用户信息，所以如果有人知道我如何做到这一点，请告诉我。

SELECT COUNT(*) 
FROM userFollower UF
INNER JOIN Users U ON U.id = UF.Userid

Answer 1

你可以试试这个

select u.username, count(*) as NumOfFollowers from Users u
inner join userFollower f on u.id = f.userid
where f.datetime > dateadd(hh, -4 ,getdate())
group by username
having count(*) >= 4

我们所做的是使用f.datetime > dateadd(hh，-4，getdate())过滤过去4小时内登录的任何追随者。

那么，我们正在计算它是否大于或等于4，希望这会有所帮助。

Answer 2

;With [Users](id , Username , datetime)
AS
(
SELECT 1,'xyz','2017-05-03 15:52:59.750' Union all
SELECT 2,'abd','2017-05-02 15:52:59.743' Union all
SELECT 3,'sdd','2017-05-02 15:52:59.737' Union all
SELECT 4,'rvv','2017-05-02 15:52:59.727' Union all
SELECT 5,'dsa','2017-05-01 15:52:59.657' Union all
SELECT 6,'edr','2017-05-01 15:52:59.647' Union all
SELECT 7,'edc','2017-05-01 15:52:59.617' Union all
SELECT 8,'wer','2017-05-01 15:52:59.550 ' 
)
,UserFollow(id , userid , FollowerId , Datetime)
AS
(
 SELECT 1,1,5,'2017-05-03 15:52:58.340' union all
 SELECT 2,1,2,'2017-05-03 15:52:58.333' union all
 SELECT 3,1,8,'2017-05-03 15:52:58.323' union all
 SELECT 4,1,7,'2017-05-03 13:52:58.123' union all
 SELECT 5,1,6,'2017-05-03 12:52:57.037' 
 )
 SELECT userid ,UserDatetime,[Datetime],FollowerId,COUNT(1)OVER() AS NewFollowersCount FROM (
 SELECT *,ROW_NUMBER()Over(order by FollowerId )Seq From
 (
 SELECT f.userid ,U.[Datetime]AS UserDatetime,f.[Datetime], MAX(F.FollowerId)Over(Partition by f.[Datetime] order by f.[Datetime] )FollowerId
  FROM [Users] U INNER JOIN UserFollow F
 ON f.userid=u.id

)Dt 
)Dt2 WHERE Dt2.Seq <5 AND ([Datetime]> DATEADD(HH,-4,[Datetime]))

输出

  userid    |UserDatetime           |[Datetime]                 |FollowerId |NewFollowersCount
    --------------------------------------------------------------------------------------------
    1       |2017-05-03 15:52:59.750|2017-05-03 15:52:58.333    |   2       |   4
    1       |2017-05-03 15:52:59.750|2017-05-03 15:52:58.340    |   5       |   4
    1       |2017-05-03 15:52:59.750|2017-05-03 12:52:57.037    |   6       |   4
    1       |2017-05-03 15:52:59.750|2017-05-03 13:52:58.123    |   7       |   4