如何在我的SQL查询中创建一个循环，该循环将查找每年的每个星期并返回每周的前10个值？

Question

我在写一个查询，它返回人每周提交的总数。喜欢下面...

Person  WeekNumber  Total 
ABC   1   12 
ADE   1   10 
ACD   1    8 
LKJ   2    15 
HJK   2    14 
FGH   2    12 

到目前为止，我有查询做除了能够从每周选择顶部的人之外的一切。我想我可能需要使用循环来做到这一点，但只是想看看是否有人可能有更好/更容易的想法？

这里是我的查询：

Select sub.Person, sub.WeekNumber, sum(sr_id_count) as TotalSRID 
from 
(
    SELECT 
    Person, 
    DATEDIFF(week, '2016-12-25', create_date) AS WeekNumber, 
    count(SR_ID) as SR_ID_COUNT 
    from [dbo].[tbl_Hist] 
    where create_date >= '01/01/2017' 
     and SR_STatus <> 'Canceled' 
     and Created_by <> 'System' 
    group by person, create_date 
) sub 
group by sub.Person, sub.WeekNumber 
order by WeekNumber, TotalSRID desc 

Answer 1

的语法是SQL Server的，所以我会认为这是你正在使用的数据库。如果是这样，你可以使用row_number()。每周获得最高的人：

select * 
from (select Person, datediff(week, '2016-12-25', create_date) AS WeekNumber, 
      count(SR_ID) as SR_ID_COUNT, 
      row_number() over (partition by datediff(week, '2016-12-25', create_date order by count(SR_ID) desc) as seqnum 
     from [dbo].[tbl_Hist] 
     where create_date >= '2017-01-01' and SR_STatus <> 'Canceled' and 
      Created_by <> 'System' 
     group by person, datediff(week, '2016-12-25', create_date) 
    ) sub 
where seqnum = 1; 

如果你想每周前10名，然后使用seqnum <= 10。