mysql - 仅在列存在时选择至少

我有一个连接3个表的查询（响应者、救护车和CADS*）。我试图显示每个呼叫的最快响应时间：
*CAD指紧急呼叫

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。
看看这里的小提琴：https://www.db-fiddle.com/f/axmTTp2BfYqTj2cNvuyDyJ/0

最佳答案

每个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

关于mysql - 仅在列存在时选择至少，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45633040/