SQL查找包括一定值的重复

Question

试图找到重复项之一的重复项(两个或更多(。尝试使用总和(案例...(，但是查询结果仅返回带有"救护车"的值。

select r.CADIncidentNumber, r.unit_type
from dw_prod.dbo.vw_unit_response r
where r.incident_arv_date >= DATEADD(day,-7, getdate())
and datediff(minute, r.incident_arv_time, r.incident_clr_time) > 5
and r.CallTypeGrp2 = 'ALS'
and r.unit_type in ('Ambulance', 'Medic', 'Paramedic Engine', 'Paramedic 
Truck', 'Paramedic Tower', 'Paramedic Rescue Engine', 'Paramedic Brush 
Engine', 'Paramedic Rescue Squad')
group by r.CADIncidentNumber, r.unit_type
having count(r.CADIncidentNumber) >= 2
and sum(case when r.unit_type = 'Ambulance' then 1 else 0 end) > 0
order by r.CADIncidentNumber
Current output:
CADIncidentNumber   Unit_type
F1800022174         Ambulance
F1800022283         Ambulance
F1800022737         Ambulance

所需的输出类似于：

CADIncidentNumber   Unit_type
F1800022174         Ambulance
F1800022174         Paramedic Engine
F1800022737         Ambulance
F1800022737         Medic

谢谢！

Answer 1

如果您想要原始行，请使用窗口函数：

select CADIncidentNumber, unit_type
from (select r.CADIncidentNumber, r.unit_type,
             sum(case when r.unit_type = 'Ambulance' then 1 else 0 end) over (partition by CADIncidentNumber) as ambulance_cnt,
             count(*) over (partition by CADIncidentNumber) as cnt
      from dw_prod.dbo.vw_unit_response r
      where r.incident_arv_date >= DATEADD(day, -7, getdate()) and
            datediff(minute, r.incident_arv_time, r.incident_clr_time) > 5 and
            r.CallTypeGrp2 = 'ALS' and
            r.unit_type in ('Ambulance', 'Medic', 'Paramedic Engine', 'Paramedic 
Truck', 'Paramedic Tower', 'Paramedic Rescue Engine', 'Paramedic Brush 
Engine', 'Paramedic Rescue Squad')
     ) r
where ambulance_cnt > 0 and cnt >= 2
order by r.CADIncidentNumber;