我这里有 2 张桌子。第一个是customer其次是会员资格
现在我只想得到一个特定的最后一条记录customer_id
从这些联合表(客户和会员)中。
这是我的客户表结构
这是我的成员资格表结构和所需的最新插入行
这里我自己尝试了一个代码
$results="";
$this->db->select('customer.*,membership.*');
$this->db->from('customer');
$this->db->join('membership', 'customer.id = membership.customer_id', 'left');
/* $this->db->order_by('membership.id','DESC');
$this->db->limit('1');*/
$query = $this->db->get();
$data = $query->result_array();
$todayDate = date("d-m-Y");
foreach ($data as $value) {
$this->db->select('customer.*,membership.*');
$this->db->from('customer');
$this->db->join('membership', 'customer.id = membership.customer_id', 'left');
$this->db->where('membership.customer_id', $value['customer_id']);
$this->db->order_by('membership.customer_id','DESC');
$this->db->limit('1');
$query = $this->db->get();
$dataa = $query->result();
foreach ($dataa as $values) {
$date1 = new DateTime($todayDate);
$date2 = new DateTime($values->end_date);
$diff=date_diff($date1,$date2);
$days = $diff->format("%a");
$pos = $diff->format("%R");
if($pos == "+" && $days >= 0){
$item[] = $values;
}
}
但它返回两个连接表中的所有行。但我只想要每个客户 ID 的最新(最后)行..!
请告诉我哪里出错了。谢谢
要根据最高 ID 从会员资格表中获取每个客户的最后记录,您可以通过调整加入部分来进行自加入会员资格,例如
$this->db->select('c.*,m.*');
$this->db->from('customer as c');
$this->db->join('membership as m', 'c.id = m.customer_id', 'left');
$this->db->join('membership as m1', 'm.customer_id = m1.customer_id AND m.id < m1.id', 'left');
$this->db->where('m1.id IS NULL', null, false)
$query = $this->db->get();
一个简单的 SQL 会是这样的
SELECT c.*,m.*
FROM customer AS c
LEFT JOIN membership AS m ON c.id = m.customer_id
LEFT JOIN membership AS m1 ON m.customer_id = m1.customer_id
AND m.id < m1.id
WHERE m1.id IS NULL
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)