sql - 如何根据非规范化列进行选择

Question

首先对模糊的标题感到抱歉，我不确定如何解释它。查看下面的查询，我想从“truck1”参加的地方提取事件。不仅是自己，还有其他车辆参加时。我确定它是直截了当的，但无法解决。

select
i.incident_number
Vehicles,
countofvehicles

FROM

(


SELECT


i.Incident_Number,
array_agg(ir.RESOURCE) as vehicle,
count(ir.RESOURCE) as countofvehicles



FROM INCIDENT as i
JOIN RESOURCE as ir on i.Incident_Number = ir.Incident_Number 

--WHERE ir.  like '%Truck1%'

GROUP BY i.Incident_Number) i

结果

incident_Number       vehicle               countofvehicle
     1             car1,car2,bike1                 3
     2             car1,car2,truck1                3
     3             truck1                          1
     4             car1                            1

如果我只想查看 truck1 发生的事件在哪里，使用 WHERE ir.RESOURCE like '%truck1% 只会带回事件编号 3 而不是事件 2，它与其他车辆一起发生。请问我该如何解决这个问题？

谢谢

Answer 1

你不需要子查询，只需要一个 having 子句:

SELECT i.Incident_Number,
       array_agg(ir.RESOURCE) as vehicle,
       count(ir.RESOURCE) as countofvehicles
FROM INCIDENT i JOIN
     RESOURCE ir 
     ON i.Incident_Number = ir.Incident_Number 
GROUP BY i.Incident_Number
HAVING SUM(CASE WHEN ir.RESOURCE like '%truck1% THEN 1 ELSE 0 END) > 1;

事实上，您可能也不需要 join，因为您从 INCIDENT 获取的唯一字段也在 RESOURCE 中:

SELECT ir.Incident_Number,
       array_agg(ir.RESOURCE) as vehicle,
       count(ir.RESOURCE) as countofvehicles
FROM RESOURCE ir 
GROUP BY ir.Incident_Number
HAVING SUM(CASE WHEN ir.RESOURCE like '%truck1% THEN 1 ELSE 0 END) > 1;