php - 如何在一个 SQL 语句中从当前行的前一行获取 "use"mysql 单元格，并显示包括第一行在内的所有行？

Question

这个问题已经有答案了:

The first html row is not being displayed (1 个回答)

已关闭 4 年前。

假设我有一个包含动态添加内容的 HTML 表格，需要如下所示:

+----+---------------------+---------------------+--------------------------+-----------------------------+
| ID |          A          |          B          |            C             | Elapsed since last checkout |
+----+---------------------+---------------------+--------------------------+-----------------------------+
|  1 | 2018-09-01 00:00:00 | 2018-09-03 00:00:00 | 2 days 0 hours 0 minutes | unknown                     |
|  2 | 2018-09-05 00:00:00 | 2018-09-06 00:00:00 | 1 days 0 hours 0 minutes | 2 days 0 hours              |
|  3 | 2018-09-06 00:00:00 | 2018-09-08 00:00:00 | 2 days 0 hours 0 minutes | 0 days 0 hours              |
+----+---------------------+---------------------+--------------------------+-----------------------------+

A 列和 B 列通过表单添加到表中。 C 列和“已用时间...”根据前一行和当前行的值进行计算。

当我尝试将表数据从 Php/MySql 输出到 HTML 中时，这就是我得到的:

+----+---------------------+---------------------+--------------------------+-----------------------------+
| ID |          A          |          B          |            C             | Elapsed since last checkout |
+----+---------------------+---------------------+--------------------------+-----------------------------+
|  2 | 2018-09-05 00:00:00 | 2018-09-06 00:00:00 | 1 days 0 hours 0 minutes | 2 days 0 hours              |
|  3 | 2018-09-06 00:00:00 | 2018-09-08 00:00:00 | 2 days 0 hours 0 minutes | 0 days 0 hours              |
+----+---------------------+---------------------+--------------------------+-----------------------------+

我正在使用这个问题的逻辑:How to get next/previous record in MySQL?组成我的 SQL 语句以从当前行的上一行中获取值，但不幸的是第一行没有显示:

"SELECT * FROM (select * from bookings WHERE id < $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";

如何显示数据库中的所有行并从当前行的上一行中选择一个mysql单元格在一条 SQL 语句中？

如果您需要浏览的话，这是我的完整代码。我正在使用嵌套查询，因为我不知道该怎么做...(我读到 JOIN 和 LEFT JOIN 但根据我的理解，只有当您使用不同的表时，情况并非如此我。)

$sqlQuery = "SELECT * FROM bookings";
$result = mysqli_query($conn, $sqlQuery);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $id = $row['id'];
        $out = $row['check_out'];
        $in = $row['check_in'];
        $sum = $row['sum'];

        $sqlQueryLastDate = "SELECT * FROM (select * from bookings WHERE id < $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";
        $resultLastDate = mysqli_query($conn, $sqlQueryLastDate);
        $resultCheckLastDate = mysqli_num_rows($resultLastDate);
        if ($resultCheckLastDate > 0) {
            while ($rowLastDate = mysqli_fetch_assoc($resultLastDate)) {
                $lastInDate = $rowLastDate['check_in'];
                //echo "previous row's in date:" .$lastInDate;

                $sqlQueryCurrentDate = "SELECT * FROM (select * from bookings WHERE id = $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";
                $resultCurrentDate = mysqli_query($conn, $sqlQueryCurrentDate);
                $resultCheckCurrentDate = mysqli_num_rows($resultCurrentDate);
                if ($resultCheckCurrentDate > 0) {
                    while ($rowCurrentDate = mysqli_fetch_assoc($resultCurrentDate)) {
                        $currentOutDate = $rowCurrentDate['check_out'];
                        //echo "current row's out date:" .$currentOutDate;

                        $lastIn = new DateTime($lastInDate);
                        $currentOut = new DateTime($currentOutDate);
                        $intervalLastCurrent = $lastIn->diff($currentOut);
                        $elapsedLastCurrent = $intervalLastCurrent->format('%a days %h hours');
                        echo "
                                <tr>    
                                    <td>".$id."</td>
                                    <td>".$out."</td>
                                    <td>".$in."</td>
                                    <td>".$sum."</td>
                                    <td>".$elapsedLastCurrent."</td> 
                                </tr>
                            ";
                    } /*$sqlQueryCurrentDate*/
                }
            } /*$sqlQueryLastDate*/
        }
    } /*$sqlQuery*/
}

?>

即使您能为我提供必要的术语来使其发挥作用，我也会做自己的研究。例如，有人建议我使用 LEFT JOIN但经过研究后发现它并不适合我想要实现的目标。

我尝试实现但无法实现的事情:

How can I subtract a previous row in sql?

SQL Previous row from the same column

(还有更多)

Answer 1

解决这个问题的正确方法是使用仅在 MySQL 8 中可用的窗口函数

如果没有窗口函数，您可以使用类似以下查询的内容:

SELECT 
  *,
  (SELECT value FROM bookings WHERE id < b.id ORDER BY id LIMIT 1) AS prev 
FROM 
  bookings b 
ORDER BY id;