mysql - 仅当列存在时才选择最少

Question

我有一个连接 3 个表(急救人员、救护车和 cads*)的查询。我正在尝试显示每个调用的最快响应时间:

*cads 指的是紧急调用

SELECT cads.date, cads.cad_id, LEAST(TIMESTAMPDIFF(SECOND,cads.date, responders.arrival_time),TIMESTAMPDIFF(SECOND,cads.date,ambulances.incident_arrival_time)) AS time
FROM cads
LEFT OUTER JOIN responders ON cads.cad_id = responders.cad_id
LEFT OUTER JOIN ambulances ON cads.cad_id = ambulances.cad_id
WHERE (cads.date >= '2017-08-10' AND cads.date <= '2017-08-12')
Group By cads.cad_id

问题是有些调用有响应者，有些有救护车，有些两者都有。上述查询仅在同时有急救人员和救护车的情况下有效，然后在所有其他时间返回 null。

在这里查看 fiddle :https://www.db-fiddle.com/f/axmTTp2BfYqTj2cNvuyDyJ/0

Answer 1

每个 cad_id 似乎有不止一个响应者或救护车值。因此，您必须首先计算每个 cad_id 的最小响应者和救护车值，然后在响应者和救护车之间获得最小值:

SELECT cad_id,
       CASE 
          WHEN amb_min_time IS NULL THEN resp_min_time
          WHEN resp_min_time IS NULL THEN amb_min_time
          ELSE LEAST(amb_min_time, resp_min_time)
       END AS time
FROM (       
   SELECT cads.cad_id,
          MIN(TIMESTAMPDIFF(SECOND,cads.date, resp.arrival_time)) AS resp_min_time, 
          MIN(TIMESTAMPDIFF(SECOND,cads.date,amb.incident_arrival_time)) AS amb_min_time
   FROM cads
   LEFT OUTER JOIN responders AS resp ON cads.cad_id = resp.cad_id
   LEFT OUTER JOIN ambulances AS amb ON cads.cad_id = amb.cad_id
   WHERE (cads.date >= '2017-08-10' AND cads.date <= '2017-08-12')
   Group By cads.cad_id) AS t

Demo here