php - 如何选择上一条记录不依赖于 MySQL 中的 AutoIncrements ID？

Question

比如说，我在 MySQL 上有一个名为 tbl_order 的表，其结构如下(仅供引用):

---------------------------------------------------------------------------------------------------------| id | grade | type  | order_date          | order_confim_time   | vol_in | vol_out | vol_dev | status  || AI | VARCH | VARCH | TIMESTAMP           | TIMESTAMP           | DOUBLE | DOUBLE  | DOUBLE  | VARCH   |---------------------------------------------------------------------------------------------------------| 81 | AAA   | IN    | 2014-09-10 09:00:00 | 2014-09-10 13:00:00 | 498000 |       0 |   -2000 | CONFIRM || 83 | AAA   | IN    | 2014-09-10 10:01:00 | 2014-09-10 14:00:00 | 998000 |       0 |   -2000 | CONFIRM || 85 | AAA   | OUT   | 2014-09-11 09:02:00 | 2014-09-11 13:00:00 |      0 |   99000 |   -1000 | CONFIRM || 87 | AAA   | OUT   | 2014-09-11 10:03:00 | 2014-09-11 14:00:00 |      0 |  145000 |   -5000 | CONFIRM || 89 | AAA   | OUT   | 2014-09-12 09:04:00 | 2014-09-12 13:00:00 |      0 |  120000 |   -5000 | CONFIRM || 91 | AAA   | OUT   | 2014-09-12 09:06:00 | NULL                |      0 |  130000 |   -2000 | NOTCONF || 93 | AAA   | OUT   | 2014-09-13 10:05:00 | 2014-09-12 14:00:00 |      0 |  115000 |   -5000 | CONFIRM |---------------------------------------------------------------------------------------------------------

Then I did the following query:

SELECT * FROM `tbl_order` WHERE `tbl_order`.`grade` = 'AAA' AND `tbl_order`.`order_confim_time` < NOW() ORDER BY `tbl_order`.`status` DESC, `tbl_order`.`order_confim_time` ASC

这样我们就可以得到如下结果:

---------------------------------------------------------------------------------------------------------| id | grade | type  | order_date          | order_confim_time   | vol_in | vol_out | vol_dev | status  || AI | VARCH | VARCH | TIMESTAMP           | TIMESTAMP           | DOUBLE | DOUBLE  | DOUBLE  | VARCH   |---------------------------------------------------------------------------------------------------------| 91 | AAA   | OUT   | 2014-09-12 09:06:00 | NULL                |      0 |  130000 |   -2000 | NOTCONF || 93 | AAA   | OUT   | 2014-09-13 10:05:00 | 2014-09-12 14:00:00 |      0 |  115000 |   -5000 | CONFIRM || 89 | AAA   | OUT   | 2014-09-12 09:04:00 | 2014-09-12 13:00:00 |      0 |  120000 |   -5000 | CONFIRM || 87 | AAA   | OUT   | 2014-09-11 10:03:00 | 2014-09-11 14:00:00 |      0 |  145000 |   -5000 | CONFIRM || 85 | AAA   | OUT   | 2014-09-11 09:02:00 | 2014-09-11 13:00:00 |      0 |   99000 |   -1000 | CONFIRM || 83 | AAA   | IN    | 2014-09-10 10:01:00 | 2014-09-10 14:00:00 | 998000 |       0 |   -2000 | CONFIRM || 81 | AAA   | IN    | 2014-09-10 09:00:00 | 2014-09-10 13:00:00 | 498000 |       0 |   -2000 | CONFIRM |---------------------------------------------------------------------------------------------------------

我确实在我的模型 Order 中编写了以下函数，以便使用递归获取上一行:

    // i made simplify code, but still the code only work on 1st previous row. the rest still not.    public function getPrevRow() {    if (is_null($this->order_confim_time))        {            $data = self::model()->find(array(                'condition' => '`t`.`grade`=:type_order AND (`t`.`order_date` '`t`.`status` DESC, `t`.`order_confim_time` DESC, `t`.`id` DESC',                'limit'     => 1,                'params'    => array('type_order' => $this->grade, ':current_date_confirm' => $this->order_confim_time),            ));            if (is_null($data)) return 0;            else return floatval($data->PrevRow) +            floatval($data->vol_in) +            floatval($data->vol_dev) -            floatval($data->vol_out);        } else {            $data = self::model()->find(array(                'condition' => '`t`.`order_confim_time` '`t`.`status` DESC, `t`.`id` DESC',                'limit'     => 1,                'params'    => array(':current_date_confirm' => $this->order_confim_time, 'type_order' => $this->grade),            ));            if (is_null($data)) return 0;            else return floatval($data->PrevRow) +            floatval($data->vol_in) +            floatval($data->vol_dev) -            floatval($data->vol_out);        }    }

结果非常好，只尊重 order_confirm_time 和 NOT NULL 值。

Current result in CGridView

The expected condition is something like this

如何在不需要依赖 ORDER BY id DESC 的情况下获取上一行？目前我依赖于 ORDER BY order_confirm_time。

我正在使用 YII 1.1.16-branch，php 5.4 @ Windows 7 x64，MariaDB 5.5.38

Answer 1

在伪代码中，你可以这样做:

1. 初始化tempv_var
2. 初始化我的数组
3. 循环记录
4. 在将记录传递到您的 my_array 之前在这里做一些条件
5. 在 my_array[temp_var] 中存储记录
6. 增加 temp_var

最后，您将拥有包含索引为 temp_var 的记录的 my_array。这是直接的解决方案，但您可以在 SQL 查询中执行此操作并在脚本中添加一些逻辑。