如果sql server中的值在过去3个月内大于3,则查询以显示数据

ubby3x7f  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(426)

如果过去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的值。
请看图片
lxkprmvk

lxkprmvk1#

你可以用Windows max() 在子查询中计算每个 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个月里,它能更有效地过滤。

相关问题