sql-server - 添加附加条件以使用计数对数据进行排序

Question

我正在编写一份当前使用以下 SQL 查询的报告。

WITH T AS
(
    SELECT *, 
       COUNT(*) OVER (PARTITION BY location) as Cnt
    FROM myTable1
)
SELECT incident_num, unit, incident_date, call_type, location
FROM T
WHERE Cnt > 4
and incident_date >= dateadd(day, -30, getdate())
order by location

此查询为我提供了一组数据，这些数据在过去 30 天内具有四个以上相同的位置。现在我需要添加另一个条件，它将查找具有四个以上事件日期的位置。

如何向此查询添加另一个条件？

标签： sql-server

Answer 1

也许尝试以下。

WITH T AS
(
SELECT *, 
   COUNT(*) OVER (PARTITION BY location) as Cnt,
   COUNT(*) OVER (PARTITION BY incident_date) as Cnt2
FROM myTable1
)
SELECT incident_num, unit, incident_date, call_type, location
FROM T
WHERE Cnt > 4
and Cnt2 > 4
and incident_date >= dateadd(day, -30, getdate())
order by location