查询以显示数据，如果该值在过去 3 个月中的任何一个月在 SQL Server 中大于 3

Question

如果过去 3 个月中的任何月份具有该值，我想过滤该值>=3。查看我在输出中突出显示的图像。

我尝试过的查询。

select Application_Infrastructure,year,month_name,count(ticket_no) as 'CN' from [service_delivery]
where month_num in (MONTH(getdate()),MONTH(getdate())-1,MONTH(getdate())-2,MONTH(getdate())-3)
group by Application_Infrastructure,year,month_name 
having count(ticket_no)>=3
order by Application_Infrastructure,CN 

having count(ticket_no)>=3正在消除该月小于 3 的值。

请看图片

Answer 1

可以使用窗口max()在子查询中计算 3 个月内每application_infrastructure的最大计数，并在外部查询中按该计数进行筛选。

select *
from (
select 
application_infrastructure, 
year,
month_name,
count(ticket_no) cn,
max(count(ticket_no)) over(partition by application_infrastructure) max_cn
from service_delivery
where datefromparts(year, month_num, 1) 
>= dateadd(month, -3, datefromparts(year(getdate()), month(getdate()), 1))
group by application_infrastructure, year, month_num, month_name
) t
where max_cn <= 3
order by application_infrastructure, cn

请注意，我重写了您的where子句，以便它正确、更有效地过滤过去 3 个月。