MySQL 子查询 - 获取最新的时间戳记录并提取字段

Question

我有一个获取表记录的查询，并计算该表的相关记录数。

我想做的是获取相关表的最新记录的 text 字段并将其包含在查询中

SELECT DISTINCT
    inst.id, inst.name, inst.state, inst.farm_status,
    (SELECT COUNT(inst_note.id) 
        FROM project_institution_note AS inst_note
        WHERE inst_note.institution_id = inst.id) AS inst_note_count,
    (SELECT COUNT(c.id) FROM project_catalog AS c
        WHERE c.institution_id = inst.id 
        AND c.status = 0 
        AND c.catalog_type BETWEEN 0 AND 1) AS ug_count,
    (SELECT COUNT(c.id) FROM project_catalog AS c
        WHERE c.institution_id = inst.id 
        AND c.status = 0 
        AND c.catalog_type BETWEEN 1 AND 2) AS grad_count,
    (SELECT COUNT(c.id) FROM project_catalog AS c
        WHERE c.institution_id = inst.id 
        AND c.status = 0 
        AND c.catalog_type >= 3) AS alt_count,
    (SELECT COUNT(c.id) FROM project_catalog_note AS cn
        INNER JOIN farmtool_catalog AS c
        ON c.id = cn.catalog_id
        WHERE c.institution_id = inst.id) AS catalog_note_count,
    (SELECT inst_note.text FROM project_institution_note AS inst_note  -- Issue portion of query
        LEFT JOIN project_institution AS inst
        ON inst_note.institution_id = inst.id
        WHERE inst_note.institution_id = inst.id
        ORDER BY inst_note.date DESC
        LIMIT 1) AS latest_note
FROM project_institution AS inst
LEFT JOIN project_institution_note AS inst_note
ON inst.id = inst_note.institution_id
LEFT JOIN project_catalog AS c
ON inst.id = c.institution_id
WHERE LOWER(inst.state) = "me";

这里发生的事情是它选取 project_institution_note 中的最后一条记录并将 text 作为查询中的值提供。有了这个子查询，我怎么能

1. 将 project_institution_note 的选择限制为相关的 project_institution.id AND
2. 选择最近的时间戳记录。

我已经完成了 #2，但我需要帮助来限制 #1 中的结果集。有什么想法吗？

Answer 1

最新记录，嗯？也就是说，project_institution_note 中具有最新 date 值的记录？

如果 project_institution_note 有一个自动递增的 id 列，并且您只插入而从不更新该表，这很容易。这是一个子查询，它获取每个 institution_id 的最新 text 值。

                  SELECT a.institution_id, a.text
                    FROM project_institution_note a
                    JOIN (   SELECT MAX(id) id
                               FROM project_institution_note
                              GROUP BY institution_id
                         ) b ON a.id = b.id

这基本上是使用每个机构的最大 id 值列表来查找最新的 text。

如果您必须使用 date 列，则稍微困难一些，但遵循相同的模式。 (如果两个 date 值相同，您将得到两个结果。)

                  SELECT a.institution_id, a.text
                    FROM project_institution_note a
                    JOIN (   SELECT institution_id,
                                    MAX(date) date
                               FROM project_institution_note
                              GROUP BY institution_id
                         ) b  ON a.date = b.date
                             AND a.institution_id = b.institution_id                    

然后您可以将其用作独立(不相关)的子查询并将其加入您的其他内容。这是一个例子

  SELECT inst.id, inst.name, inst.state, inst.farm_status,
         note.text
    FROM project_institution inst
    LEFT JOIN (
                /* the subquery from above */
         ) note ON inst.id = note.institution_id

使用 LEFT JOIN 而不是 JOIN 可以防止查询抑制不存在注释的结果。

您的查询中有很多依赖子查询。 MySQL 在依赖子查询方面很糟糕。您可能希望将它们重构为独立的子查询并加入它们。