sql - 如果 SQL Server 中最近 3 个月的任何一个值大于 3,则查询以显示数据
问题描述
如果过去 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 的值。
解决方案
您可以在子查询中使用窗口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 个月内正确且更有效地过滤。
推荐阅读
- powershell - 查找两个文件的差异并附加到第一个文件 powershell
- c# - 如何在 .NET Core Web API 的 Dictionary 键中将枚举转换为 int
- javascript - 如何通过执行切片用地图重写推送?
- reactjs - 在类组件中反应 useEffect 等效项
- deep-learning - 是否可以在 FPGA 上训练 YOLO - 对象检测模型?
- javascript - next() 在 Express 4 上不起作用,错误 [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
- docker - launch ElasticSearch in Docker Plesk
- django - Django filter many to many list of objects that contains all the items of a list
- html - 如何通过使 div(及其内容)更小来防止文本中断?
- curl - unexpectedly website returns redirection to itself