mysql - 选择两个表并按日期排序

Question

我试图列出两个表doc_to_do和doc_bug_tracker的最新更新，下面是我的表结构

doc_to_do

doc_bug_tracker

这是我当前的查询:

$sth = $this->db->prepare('SELECT p.*, 
        dtd.projects_id as dtd_projects_id, dtd.content as dtd_content, dtd.date_modified as dtd_date_modified,
        dbt.projects_id as dbt_projects_id, dbt.content as dbt_content, dbt.date_modified as dbt_date_modified 
        FROM `projects` p LEFT JOIN `doc_to_do` dtd ON p.id=dtd.projects_id 
        LEFT JOIN `doc_bug_tracker` dbt ON p.id=dbt.projects_id 
        where p.id="'.$project_id.'"');

那么现在如何从表 doc_to_do 或 doc_bug_tracker 中按 date_modified 进行排序？

Answer 1

要仅获取最新日期(而不是所有日期，按降序排列)，请尝试以下操作:

SELECT
  p.id,
  MAX(GREATEST(dtd.date_modified, dbt.date_modified)) AS MaxDate
FROM projects p
LEFT JOIN doc_to_do dtd ON p.id = dtd.projects_id
LEFT JOIN doc_bug_tracker dbt ON p.id = dbt.projects_id
WHERE p.id = <project_id>
GROUP BY p.id

如果您的 SELECT 中需要其他列，请务必将它们包含在 GROUP BY 中。