使用子查询选择 MAX 值以及 LEFT JOIN

2022-01-23 00:00:00 join sql subquery mysql

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

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 individual_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.

相关文章