sql - 为上个月的每个组选择 10%

Question

我可以使用前一个月的前 10%

select top 10 percent ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt from Tbl 
where DATEPART(m, Entered_Date) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(yyyy, Entered_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate())) and 
CreatedBy ='User1')
order by amt DESC

对于使用联合的每个用户，我必须多次执行此操作，如何在一个查询中执行此操作？当我添加其他用户 CreatedBy in('User1','User2') 它不起作用。我查看了分区上的行，但无法弄清楚。我正在使用 SSMS 2017。

更新如下

Select * From(
select ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt 
NTILE (10) OVER ( PARTITION BY CREATEDBY ORDER BY Amt desc)  AS PercentageNo
from Tbl 
where DATEPART(m, Entered_Date) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(yyyy, Entered_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate())) 
/*Entered_Date between DATEADD(m, -2, getdate())  and DATEADD(m, -1, getdate()) */ )as SubQuery
where PercentageNo=1 order By Amt

标签： sqlsql-server

Answer 1

您可以使用GROUP BY每个用户的选择百分比，如下所示：

select      top 10 percent ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt 
FROM Tbl 
where       DATEPART(m, Entered_Date) = DATEPART(m, DATEADD(m, -1, getdate())) 
            AND DATEPART(yyyy, Entered_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate())) 
            AND CreatedBy IN ('User1','User2')
GROUP BY    ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt
order by    amt DESC