使用子查询来选择最大值和左联接。

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中有不止一个记录，这就是我想要得到最大值(最新id)的地方。

我尝试了许多不同的查询，但总是得到对非对象上的成员函数rowCount()的错误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。

顺便说一句，将表别名与表名同名是多余的。这只会把查询弄得一团糟。另外，对别名使用表缩写也是个好主意，比如ia表示individuals_achievements。因为这个答案只关注from子句，所以我没有做这个更改。

Answer 2

如果在处理查询时出现错误，PDO::query将返回一个FALSE，并且将rowCount作为成员函数应用于FALSE将导致所看到的错误。

检查查询中是否存在语法错误，并解析PDO将返回的任何错误消息。

Answer 3

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

它应该是

INNER JOIN (SELECT blah) AS something ON individuals.unique_id=something.individual_id