php - 如何查询列中具有特定值的最新记录

Question

我有一个学校数据库(只有 3 个)。在数据库中，提交有关其性能的记录。

我想要一个选项让用户只查询每所学校的最新条目。

此刻-我可以查询最近的整体...

$result = mysqli_query($con,"SELECT * FROM reports, academicyears,schools,terms
WHERE reports.report_id=(SELECT MAX(report_id) FROM reports WHERE school_id=1 OR school_id=2 OR school_id=3)
AND academicyears.ay_id=reports.ay_id
AND schools.school_id=reports.school_id
AND terms.term_id=reports.term_id
ORDER BY reports.datesubmitted DESC

但我希望能够同时找到学校 1、学校 2 和学校 3 的最新信息。

我可以看到 OR 没有给我我想要的...它只返回最新记录!查询应该是什么？

另外 - 下一步是找到最近的记录，而无需命名学校 ID。随着学校数量的增长——那将是完全没有效率的!这可能吗？

Answer 1

像这样的查询应该可以解决问题。这是三个独立的查询，每个学校 1 个，由 UNION 命令放在一起:

FROM (
    (
        SELECT * 
        FROM reports, academicyears, schools, terms
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE school_id=1
        )
        AND academicyears.ay_id=reports.ay_id
        AND schools.school_id=reports.school_id
        AND terms.term_id=reports.term_id
    ) UNION (
        SELECT * 
        FROM reports, academicyears, schools, terms
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE school_id=2
        )
        AND academicyears.ay_id=reports.ay_id
        AND schools.school_id=reports.school_id
        AND terms.term_id=reports.term_id
    ) UNION (
        SELECT * 
        FROM reports, academicyears, schools, terms
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE school_id=3
        )
        AND academicyears.ay_id=reports.ay_id
        AND schools.school_id=reports.school_id
        AND terms.term_id=reports.term_id
    )
) AS Latest
ORDER BY datesubmitted DESC

从技术上讲，这是一次运行 6 个查询，因此效率不会非常高。随着条目数量的增加，这个查询会变慢。