使用子查询与LEFT JOIN一起选择MAX值 [英] Using sub-query to SELECT MAX value along with LEFT JOIN
问题描述
我有一个查询来获取搜索结果,效果很好.
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.
这篇关于使用子查询与LEFT JOIN一起选择MAX值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!