php - mysql按降序查找不同状态的条目数

Question

我有一张这样的 table

+----+--------+--------+
| id | ref_id | status |
+----+--------+--------+
|  1 |      3 |      1 |
|  2 |      4 |      0 |
|  4 |      6 |      0 |
|  6 |      8 |      1 |
|  8 |     10 |      0 |
| 10 |     12 |      1 |
| 12 |     14 |      1 |
| 14 |     16 |      0 |
| 16 |     18 |      0 |
| 18 |     20 |      0 |
+----+--------+--------+

我想按降序连续查找状态为 0 的行数。在这种情况下，数字为 3

    +----+--------+--------+
    | id | ref_id | status |
    +----+--------+--------+
    |  1 |      3 |      1 |
    |  2 |      4 |      0 |
    |  4 |      6 |      0 |
    |  6 |      8 |      1 |
    |  8 |     10 |      1 |
    | 10 |     12 |      1 |
    | 12 |     14 |      0 |
    | 14 |     16 |      0 |
    | 16 |     18 |      0 |
    | 18 |     20 |      0 |
    +----+--------+--------+

在本例中是四个

 +----+--------+--------+
    | id | ref_id | status |
    +----+--------+--------+
    |  1 |      3 |      1 |
    |  2 |      4 |      0 |
    |  4 |      6 |      0 |
    |  6 |      8 |      1 |
    |  8 |     10 |      1 |
    | 10 |     12 |      1 |
    | 12 |     14 |      0 |
    | 14 |     16 |      0 |
    | 16 |     18 |      0 |
    | 18 |     20 |      1 |
    +----+--------+--------+

在本例中为 0

到目前为止，我尝试处理降序的行并通过 php 条件找到它。有更好的方法吗？

Answer 1

我在 codeigniter 中使用了这段代码并得到了结果

$this->db->select('count(id) as number');
$this->db->from('tablename');
$this->db->where('id > (select max(id) FROM tablename where status = 1)',
NULL, FALSE);

在数据库中

SELECT count(id) as number FROM tablename
WHERE id > (select max(id) FROM tablename where status = 1)