php - 我们如何将这两个表合并为一个

在这里我想将这两个查询结果合并为一个结果
第一个查询看起来像这样

   $this->db->select('bills.date as d_date,bill_details.agent_name,
   SUM(bill_details.profit) AS total_profit');
   $this->db->join('bill_details', 'bill_details.bill_id=bills.id','left');
   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date);
   $this->db->group_by('Date(bills.date)');
   $this->db->group_by('bills.customerid');
   $query1 = $this->db->get('bills')->result();
   return $query1;

结果看起来像这样

Name    Date          Purchase
Ned     2019-07-26      210.60

第二个查询看起来像这样

    $this->db->select('assigned_result.date,assigned_result.user_id,SUM(assigned_result.total_price) AS t_price,SUM(assigned_result.total_dc) AS t_dc');
    $this->db->where('DATE(assigned_result.date) >=', $start_date);
    $this->db->where('DATE(assigned_result.date) <=', $end_date);

    $this->db->group_by('assigned_result.user_id');
    $query2 = $this->db->get('assigned_result')->result();

第二个查询结果看起来像这样

Name    Date          winning
Ned     2019-07-26      120

现在我想结合这样的查询

Name    Date          Purchase   winning
Ned     2019-07-26      210.60         120

为了获得此结果，我加入了查询并像这样返回

$this->db->select('bills.date as d_date,bill_details.agent_name,
   SUM(bill_details.profit) AS total_profit,SUM(assigned_result.total_price) AS t_price,SUM(assigned_result.total_dc) AS t_dc');
   $this->db->join('bill_details', 'bill_details.bill_id=bills.id','left');
   $this->db->join('assigned_result', 'bills.id=assigned_result.bill_no', 'left');
   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date);
   $this->db->group_by('Date(bills.date)');
   $this->db->group_by('bills.customerid');
   $query1 = $this->db->get('bills')->result();
   return $query1;

但是得到这样的结果

Name    Date          Purchase  winning
Ned     2019-07-26      226      160

purchase amount弄错了。

最佳答案

根据您的SQL Editor数据，下一个查询适合您：

select
   B.user_id,
   B.agent_name,
   sum(B.profit),
   C2.tprice,
   C2.tdc
from B
      join A on A.id = B.bill_id and A.customerid = B.user_id
 left join ( select
                  C.user_id,
                  C.data_id,
                  C.bill_no,
                  sum(C.total_price) tprice,
                  sum(C.total_dc) tdc from C) C2 on C2.data_id = B.id and C2.user_id = B.user_id
group by B.user_id;

PHP：（reference）

    $this->db->select('DATE(bills.date) as d_date,
                      bill_details.user_id,
                      bill_details.agent_name,
                      SUM(bill_details.profit) AS total_profit,
                      C2.tprice AS t_price,
                      C2.tdc AS t_dc');

   $this->db->join('bills', 'bills.id = bill_details.bill_id and bills.customerid = bill_details.user_id','left');
   $this->db->join('(select
                assigned_result.user_id,
                assigned_result.data_id,
                assigned_result.bill_no,
                sum(assigned_result.total_price) tprice,
                sum(assigned_result.total_dc) tdc from assigned_result) as C2','C2.data_id = bill_details.id and C2.user_id = bill_details.user_id', 'LEFT');

   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date);

   $this->db->group_by('bill_details.user_id)');

   $query1 = $this->db->get('bill_details')->result();
   return $query1;

要么

    $this->db->select('assigned_result.user_id,
                assigned_result.data_id,
                assigned_result.bill_no,
                sum(assigned_result.total_price) tprice,
                sum(assigned_result.total_dc) tdc')
            ->from('assigned_result');

    $subquery = $this->db->_compile_select();
    $this->db->_reset_select();

    $this->db->select('DATE(bills.date) as d_date,
                      bill_details.user_id,
                      bill_details.agent_name,
                      SUM(bill_details.profit) AS total_profit,
                      C2.tprice AS t_price,
                      C2.tdc AS t_dc');

   $this->db->join('bills', 'bills.id = bill_details.bill_id and bills.customerid = bill_details.user_id','left');
   $this->db->join("($subquery) C2",'C2.data_id = bill_details.id and C2.user_id = bill_details.user_id', 'LEFT');

   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date);

   $this->db->group_by('bill_details.user_id)');

   $query1 = $this->db->get('bill_details')->result();
   return $query1;