使用子查询与LEFT JOIN一起选择MAX值

Question

我有一个查询来获取搜索结果，效果很好。

成功查询的示例：

 SELECT
    individuals.individual_id,
    individuals.unique_id,
    TIMESTAMPDIFF(YEAR,individuals.day_of_birth,CURDATE()) AS age,
    individuals_dynamics.id,
    individuals_achievements.degree
  FROM
    individuals as individuals
  LEFT JOIN
    individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id
  LEFT JOIN
    individuals_achievements AS individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id
  WHERE
    $uuid_access_status $display_type $detailed_search_query
  ORDER BY 
    $search_sort $search_order

从现在开始，我individuals_achievements每个人都有一个以上的记录，这是我想要获得MAX值（最新ID）的地方。

我尝试了许多不同的查询，但始终收到错误消息 _ ，在非对象上调用成员函数rowCount（）。_

我了解该错误的含义，但我无法弄清楚该错误的出处和总体上的错误。

我未成功尝试的示例：

  SELECT
    individuals.individual_id,
    individuals.unique_id,
    TIMESTAMPDIFF(YEAR,individuals.day_of_birth,CURDATE()) AS age,
    individuals_dynamics.id,
    individuals_achievements.degree
  FROM
    individuals as individuals
  LEFT JOIN
    individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id
  INNER JOIN
  (
       SELECT
            degree, MAX(id) AS latest_record
       FROM
            individuals_achievements
       GROUP BY
            latest_record
  ) individuals_achievements AS individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id
  WHERE
    $uuid_access_status $display_type $detailed_search_query
  ORDER BY 
    $search_sort $search_order

我在这里想念什么？有什么帮助吗？

Answer 1

这是你的from条款：

  FROM
    individuals as individuals
  LEFT JOIN
    individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id
  INNER JOIN
  (
       SELECT
            degree, MAX(id) AS latest_record
       FROM
            individuals_achievements
       GROUP BY
            latest_record
  ) individuals_achievements AS individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id;

我至少可以发现三个问题。首先是individuals_achievements AS individuals_achievements;
第二个是individuals_achievements.individual_id子查询中没有的引用。第三是group by latest_record。

  FROM individuals LEFT JOIN
       individuals_dynamics
       ON individuals.unique_id = individuals_dynamics.individual_id LEFT JOIN
       individuals_achievements
       ON individuals.unique_id = individuals_achievements.individual_id JOIN
       (SELECT ia.individual_id, MAX(ia.id) AS latest_record
        FROM individuals_achievements ia
        GROUP BY ia.individual_id
       ) iamax
       ON individuals.unique_id = iamax.individual_id and
          individuals_achievements.id = iamax.latest_record

这将添加一个附加子查询，并带有最新记录的ID。

顺便说一句，让表别名与表名同名是多余的。那只会使查询混乱。同样，对别名使用表缩写（例如iafor）也是一个好主意individuals_achievements。因为此答案仅针对from条款，所以我没有进行更改。