php - 如何获取联接中每个bank_id的最新记录

我正在使用Codeigniter，但有一个无法正常运行的查询。
我有3个名为tbl_lead, tbl_bankdata, tbl_payment_invoice的表。两个表联接没有问题。我在表tbl_payment_invoice上遇到了问题。

问题是，我正在tbl_payment_invoice表中插入付款状态。它可以是bank_id的多个状态。

我必须显示tbl_payment_invoice中用户的最新状态。

tbl_payment_invoice表结构

p_i_id | payment_invoice|payment_invoice_date |bank_id |payment_invoice_by
1      | 1              | 2019-09-29 20:39:12 |33      |1
2      | 2              | 2019-09-29 20:39:43 |33      |1
3      | 1              | 2019-09-29 20:40:02 |05      |1
4      | 1              | 2019-09-29 20:45:12 |20      |1
5      | 2              | 2019-09-29 20:50:38 |05      |1
6      | 2              | 2019-09-29 21:10:12 |20      |1
7      | 1              | 2019-09-29 21:15:40 |10      |1

我的预期输出来自此表是

p_i_id | payment_invoice|payment_invoice_date |bank_id |payment_invoice_by
2      | 2              | 2019-09-29 20:39:43 |33      |1
5      | 2              | 2019-09-29 20:50:38 |05      |1
6      | 2              | 2019-09-29 21:10:12 |20      |1
7      | 1              | 2019-09-29 21:15:40 |10      |1

我必须显示bank_id的最新记录。

您能帮我了解如何与join一起使用吗？

public function disbursed_Leadlist(){
if($this->session->userdata['login_session']['access_role']==5){
   $or_where="tbl_bankdata.b_filestatus=1  AND createby='".$this->session->userdata['login_session']['id']."'";
}
else{
    $or_where="tbl_bankdata.b_filestatus=1";
}

$result = $this->db->select('*')
                  ->from('tbl_lead')
                  ->join('tbl_bankdata','tbl_lead.c_id=tbl_bankdata.lead_id','LEFT')
                  ->join('tbl_payment_invoice','tbl_bankdata.bank_id=tbl_payment_invoice.bank_id','LEFT')
                  ->where($or_where)
                  ->order_by('tbl_lead.date_of_created','DESC')
                  ->get()
                  ->result();
                       // echo $this->db->last_query();
                  return $result;
  }

最佳答案

我将max（payment_invoice）更改为max（payment_invoice）作为payment_invoice，我认为这种扭曲将起作用。

$result = $this->db->select('*')
        ->from('tbl_lead')
        ->join('tbl_bankdata','tbl_lead.c_id=tbl_bankdata.lead_id','LEFT')
        ->join('tbl_bankname','tbl_bankname.b_id=tbl_bankdata.b_bankname')
        ->join('(select bank_id, max(payment_invoice) as payment_invoice, max(payment_invoice_date) as dateofadded from tbl_payment_invoice group by bank_id) as p_i','tbl_bankdata.bank_id=p_i.bank_id','LEFT')
        ->where($or_where)
        ->order_by('tbl_lead.date_of_created','DESC')
        ->get()
        ->result();

        print_r($result);