我有一个表，需要在同一列中减去两个值，然后在另一个视图表中显示差异。我需要记录5台压缩机的每日总工时计读数，并将差值显示为每日运行小时数。
我的表：crudtabe的图像输出

SELECT a.cuidad_id,
       a.bc100a,
       Coalesce(a.bc100a - (SELECT b.bc100a
                            FROM   cuidadrun b
                            WHERE  a.cuidad_id = b.cuidad_id + 1), a.bc100a) AS
       diffbc100a
FROM   cuidadrun a

在phpmyadmin中，什么工作得很好，请参见图片：sql输出
我无法训练控制器、模型和视图以显示结果。另外，我可以将结果保存在另一个表中吗？
我的控制器

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Cactushrs extends CI_Controller {

    public function __construct()
    {
        parent::__construct();
        $this->load->model('Cactushrs_model');
    }
    public function index()
    {

        $this->load->view('template/header');
    $data = $this->Cactushrs_model->cactushrs();
    $this->load->view('pages/cactushrs_view', $data);
        $this->load->view('template/footer');
    }
}

我的模型

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Cactushrs_model extends CI_Model
{
  public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }
  public function cactushrs()
  {

    $query = $this->db->get ('SELECT a.cuidad_id, a.bc100a, COALESCE(a.bc100a - (SELECT b.bc100a FROM cuidadrun b WHERE a.cuidad_id = b.cuidad_id + 1), a.bc100a) AS differnce FROM cuidadrun a');

  }
}

我的观点

<table id="cuidadhrs" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
  <thead>
      <tr>
          <th>cuidad_id</th>
          <th>bc100a</th>
          <th>diff100</th>
      </tr>
  </thead>
  <tbody>
    <tr>
      <td><?php echo $this->db->get('differnce'); ?></td>
    </tr>
  </tbody>

小提琴https://www.db-fiddle.com/f/vsm6ha2wa3cprjaqeodmkz/0