如何在codeigniter中使用"MAX“？
如何在codeigniter中使用"MAX“？
提问于 2015-03-20 13:21:57
对于select中的行集select max元素有$this->db_selec_max();，但我想在where中使用max。如何在codeigniter中使用"MAX“？我想将以下查询转换为codeigniter形式
我的问题
SELECT rq_id, rq_plant_4sale_code, rq_serial_number, rq_quantity_requested, rq_requester_farm_id,
rq_fulfiller_farm_id, rqpri_description, rqrem_remark,rqrem_remark_datetime, rq_created_datetime,
plsal_name_botanical, plsal_name_english, pot_code, rqpri_description
FROM (reqn_requisitions)
LEFT JOIN reqn_requisition_priorities ON(rq_priority_rank=rqpri_rank)
LEFT JOIN reqn_requisition_remarks ON(rq_id=rqrem_reqn_id)
LEFT JOIN tukai_plants_4sale ON(rq_plant_4sale_code=plsal_id)
LEFT JOIN tukai_pots ON(plsal_pot_id=pot_id)
WHERE rqrem_remark_datetime IN(SELECT MAX(rqrem_remark_datetime) AS dt FROM reqn_requisition_remarks GROUP BY rqrem_reqn_id ) AND rq_challan_id=0'我正试着这样做
$this->db->select("rq_id, rq_plant_4sale_code, rq_serial_number, rq_quantity_requested, rq_requester_farm_id,
rq_fulfiller_farm_id, rqpri_description, rqrem_remark,rqrem_remark_datetime, rq_created_datetime,
plsal_name_botanical, plsal_name_english, pot_code, rqpri_description ");
$this->db->join('tukai_plants_4sale',
'tukai_plants_4sale.plsal_id = reqn_requisitions.rq_plant_4sale_code','left');
$this->db->join('tukai_pots',
'tukai_pots.pot_id = tukai_plants_4sale.plsal_pot_id','left');
$this->db->join('reqn_requisition_remarks',
'reqn_requisition_remarks.rqrem_reqn_id = reqn_requisitions.rq_id','left');
$this->db->order_by("rq_id","desc");回答 1
Stack Overflow用户
回答已采纳
发布于 2015-03-20 16:00:05
对于您可以使用的复杂where clause条件，
$this->db->where('<where condition here>', NULL, FALSE);从docs:
$ this-> db->其中（）接受可选的第三参数。 如果将其设置为False，则CodeIgniter将不会尝试使用Backticks保护您的字段或表名。
尝试下面的代码：
/* Replace table_name with appropriate table names */
$this->db->select("table_name.rq_id, table_name.rq_plant_4sale_code, table_name.rq_serial_number, table_name.rq_quantity_requested, table_name.rq_requester_farm_id,
table_name.rq_fulfiller_farm_id, table_name.rqpri_description, rqrem_remark,table_name.rqrem_remark_datetime, table_name.rq_created_datetime,
table_name.plsal_name_botanical, table_name.plsal_name_english, table_name.pot_code, table_name.rqpri_description ");
$this->db->from('reqn_requisitions');
$this->db->join('reqn_requisition_priorities','reqn_requisitions.rq_priority_rank = reqn_requisition_priorities.rqpri_rank','left');
$this->db->join('reqn_requisition_remarks','reqn_requisition_remarks.rqrem_reqn_id = reqn_requisitions.rq_id','left');
$this->db->join('tukai_plants_4sale','tukai_plants_4sale.plsal_id = reqn_requisitions.rq_plant_4sale_code','left');
$this->db->join('tukai_pots','tukai_pots.pot_id = tukai_plants_4sale.plsal_pot_id','left');
$this->db->where('reqn_requisition_remarks.rqrem_remark_datetime IN(SELECT MAX(rqrem_remark_datetime) AS dt FROM reqn_requisition_remarks GROUP BY rqrem_reqn_id', NULL, FALSE);
$this->db->where('table_name.rq_challan_id', 0);
$this->db->order_by("table_name.rq_id","desc");
$query = $this->db->get();
return $query->result();a alternative:
如果您的查询（无论如何）无法使用CI's Active Record编写，则可以始终使用一个简单的方法：
$this->db->query('<your SQL query here>');例如：
$query = $this->db->query('SELECT rq_id, rq_plant_4sale_code, rq_serial_number,rq_quantity_requested, rq_requester_farm_id,rq_fulfiller_farm_id, rqpri_description,rqrem_remark,rqrem_remark_datetime, rq_created_datetime,
plsal_name_botanical, plsal_name_english, pot_code, rqpri_description
FROM (reqn_requisitions)
LEFT JOIN reqn_requisition_priorities ON(rq_priority_rank=rqpri_rank)
LEFT JOIN reqn_requisition_remarks ON(rq_id=rqrem_reqn_id)
LEFT JOIN tukai_plants_4sale ON(rq_plant_4sale_code=plsal_id)
LEFT JOIN tukai_pots ON(plsal_pot_id=pot_id)
WHERE rqrem_remark_datetime IN(SELECT MAX(rqrem_remark_datetime) AS dt FROM reqn_requisition_remarks GROUP BY rqrem_reqn_id ) AND rq_challan_id=0');
return $query->result();页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29159929
复制相关文章
相似问题