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

本文介绍了使用子查询与LEFT JOIN一起选择MAX值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

I have a query for getting search results, which works fine.

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)的地方.

From now on, I have more than one record in individuals_achievements per each individual and this is the where I would like to get the MAX value (latest id).

我尝试了许多不同的查询，但总是收到错误 在非对象上调用成员函数rowCount().

I tried the many different queries but always was getting an error Call to a member function rowCount() on a non-object.

我了解该错误的含义，但我不知道自己在哪里犯该错误以及总体上是什么错误.

I understand what that error means but I can't figure out where I'm making that mistake and what is wrong in general.

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

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

What am I missing here? Any help please?

推荐答案

这是您的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.

I can spot at least three problems. The first is individuals_achievements AS individuals_achievements; the second is the reference to individuals_achievements.individual_id which isn't in the subquery. The third is the 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.

This adds an additional subquery, with the id of the latest record.

顺便说一句，让表别名与表名同名是多余的.那只会使查询混乱.同样，对别名使用表缩写(如ia表示individuals_achievements)也是一个好主意.因为此答案仅针对from子句，所以我尚未进行更改.

By the way, it is redundant to have a table alias be the same name as the table name. That just clutters up the query. Also, it is a good idea to use table abbreviations for the aliases, such as ia for individuals_achievements. Because this answer focuses only on the from clause, I have not made that change.