mysql - 限制在 MySQL 问题的左连接中返回的行

Question

我有两个表 trackings 和 responses。我正在运行下面的查询以根据 case/code_2 列连接两个表。

因为对于 trackings 表中的每条记录，response 表中有时会有多条记录，所以我只想返回一行，而不是为通常会发生的响应表。

我使用下面的查询完成了这项工作，效果很好。

    SELECT T0.timestamp AS 'Creation Date', T0.ipaddress, T0.code_1 AS 'Alias', T0.code_2 AS 'Case ID', COUNT(T0.ipaddress) AS each_amount, T0.first, MAX(T1.res_id) AS 'responses'
    FROM `trackings` AS T0
    LEFT JOIN `responses` AS T1 
    ON T0.code_2 = T1.case

    JOIN (
        SELECT T2.case, MAX(T2.timestamp) AS max_date
        FROM `responses` AS T2
        GROUP BY T2.case
    ) x_temp_response_table

    ON x_temp_response_table.case = T1.case
    AND x_temp_response_table.max_date = T1.timestamp

    WHERE T0.timestamp >= '2014-04-20 00:00:00'
    AND T0.timestamp <= '2014-04-30 23:59:59'
    GROUP BY code_2

但是，由于第二个连接将响应行限制为只有一个，因此当响应表中没有相应记录时，它现在不会返回 trackings 行。

基本上在添加第二个连接之前，它会返回 trackings 表中的所有行，如果 responses 中没有相应的行，则只需在“responses”列中添加一个 NULL table <- 这可能是显而易见的，因为这是左连接的作用:-)

理想情况下，我希望上面的查询仍然返回 trackings 表中的所有行，即使响应表中没有相应的行。

非常感谢任何帮助。

Answer 1

你可以用一个糟糕的子查询来做到这一点(性能不高，但是)......

SELECT 
 T0.timestamp AS 'Creation Date', 
 T0.ipaddress, T0.code_1 AS 'Alias', 
 T0.code_2 AS 'Case ID', 
 COUNT(T0.ipaddress) AS each_amount, 
 T0.first,
 (SELECT r.res_id from responses r
  where r.case = T0.code_2
  order by r.timestamp desc
  LIMIT 1) as responses

FROM `trackings` AS T0
WHERE T0.timestamp >= '2014-04-20 00:00:00'
AND T0.timestamp <= '2014-04-30 23:59:59'
GROUP BY code_2