从一个数据库(MYSQL PHP)的多个表中选择最新的记录 - selecting the latest record from multiple tables in one database (MYSQL PHP)

我有一个学校mysql数据库，有15个表(15个教室)，每个表有统一的字段/简单数据，像这样:ID, DATE, TIME, CLASSROOM_NO, STUDENTS_ALL, STUDENTS_CURRENT, COMMENTS

方法是能够一直更新这些数据通过PHP形式增加db，这很好，它工作得很好

在另一个PHP页面中，我有display.php，它只显示最近添加的记录，所以我可以知道最近对这个教室做了更新，这也很好，这正是我需要的，这是我的代码:

<?php
$con=mysqli_connect("localhost","classroom","mypsassword","mysqluser");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
$result = mysqli_query($con,"SELECT id, date, time, classroom_no, students_all,     student_current,comments FROM classroom_1 ORDER BY id DESC LIMIT 1");

while($row = mysqli_fetch_array($result))
 {
 echo "<b>Room:</b> " . $row['classroom_no'] . "<br>";
 echo "<b>All students:</b> " . $row['students_all'] . "<br>";
 echo "<b>Available students:</b> " . $row['students_current'] . "<br>";
 echo "<b>Absent:</b> ";
 echo floatval($row['students_all'])  -  floatval($row['students_current']);
 echo "<br>";
 echo "<b>Updated on:</b> " . $row['date'] ."<br>";
 echo "<b>Update time:</b> " . $row['time']."<br>" ;
  }
mysqli_close($con);
?>

现在，我在一个页面上实现和检索每个教室的最新更新没有问题…我所奋斗的是从每个表中选择最新的记录，然后在同一页面中相应地显示它们。PHP页面写入all_classroom。PHP

并在一页中显示所有教室(仅来自每个表的最新记录更新)，因此我无法理解如何同时从所有表中选择SQL查询并将其限制为最新记录…

我的表按照如下顺序排列:classroom_1classroom_2classrrom_3

请告诉

最好重新设计数据库，一个表用于教室，另一个表用于类(每个教室多行)，等等。

然而，如果这是不可能的，你需要这样的东西:-

SELECT classroom, id, date, time, classroom_no, students_all, student_current, comments
FROM
(
    SELECT '1' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_1
    UNION
    SELECT '2' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_2
    UNION
    SELECT '3' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_3
    UNION
    SELECT '4' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_4
    UNION
    SELECT '5' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_5
    UNION
    SELECT '6' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_6
    UNION
    SELECT '7' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_7
    UNION
    SELECT '8' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_8
    UNION
    SELECT '9' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_9
    UNION
    SELECT '10' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_10
    UNION
    SELECT '11' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_11
    UNION
    SELECT '12' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_12
    UNION
    SELECT '13' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_13
    UNION
    SELECT '14' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_14
    UNION
    SELECT '15' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_15
) Sub1
INNER JOIN
(
    SELECT classroom, MAX(id) AS maxid
    FROM
    (
        SELECT '1' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_1
        UNION
        SELECT '2' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_2
        UNION
        SELECT '3' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_3
        UNION
        SELECT '4' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_4
        UNION
        SELECT '5' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_5
        UNION
        SELECT '6' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_6
        UNION
        SELECT '7' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_7
        UNION
        SELECT '8' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_8
        UNION
        SELECT '9' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_9
        UNION
        SELECT '10' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_10
        UNION
        SELECT '11' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_11
        UNION
        SELECT '12' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_12
        UNION
        SELECT '13' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_13
        UNION
        SELECT '14' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_14
        UNION
        SELECT '15' AS classroom, id, date, time, classroom_no, students_all, student_current, comments FROM classroom_15
    ) Sub3
    GROUP BY classroom
) Sub2
ON Sub1.classroom = Sub2.classroom AND Sub1.id = Sub2.maxid

注意，我在UNION中添加了一个固定的教室列。这可能会复制classroom_no的值。

还涉及到一个名为students_all的列。这表明您有一个包含学生列表的列。如果是这样的话，最好将其拆分为另一个表，每个类可以有许多行(每个学生一个类)

根据Kickstart的建议:我建议您规范化您的数据库设计。把所有的教室数据放到一个表中，用一列显示每一行与哪个教室相关。

我不推荐的其他(变通)解决方案是创建一个视图。这看起来像

CREATE VIEW allData AS
SELECT * from Classroom1
UNION ALL SELECT * from Classroom2
....

然后写入select from this view